Access qry. Commission Brackets

[dcwave]

I have a table that holds a salesman name, the date the item was sold and the amount an item was sold at.

I need to spit out each month a line item report that shows the above information with a year to date column as well.

Example:
Salesman DateSold Amount YTD
Bob 05/20/04 100 100
Bob 05/21/04 200 300
Bob 06/11/04 100 400



I do not know how to get the YTD to display. I CAN get the YTD to show up in each row (400) but not the breakdown.

The reason for this is because the commission payout for each sale is based on a bracket tied to the line item year to date amount.

Any help is greatly appreciated.
dc

 

[PHV]

Something like this ?
SELECT Salesman, DateSold, Amount,
(SELECT Sum(B.Amount) FROM yourTable B WHERE B.Salesman=A.Salesman AND B.DateSold<=A.DateSold) As YTD
FROM yourTable A
ORDER BY Salesman, DateSold;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244