That would be done with a report as stated. Or in Excel if you like. It is not really something you would do in a query (it is feasible if you have a key that you can use for ordering and filtering , but very inefficient SQL).
I probably shouldn't overstate the inefficiency - it would probably bad for large tables (in excess of a hundred thousand records) and probably not too bad for small tables (less than 10000 rows). Not really sure about the in between size.
This is what is called a correlated query which mean the engine has to calculate a result for every row because of the type of link created here between the inner query and the outer query (in this case, specifically, there is a new TransDate criteria for each row, and a query for the max on the qty up to that TransDate).