Running Total based on Sales Person and Date Sold

[dcwave]

Greetings,
I am a SQL noob. I am working in Access.
I am moving a spreadsheet that housed the sales/commission data to Access.

The commission is paid out in a stair-step type of plan; i.e. $1 to $100 = 10% $101 to $500 = 20%.

Your sales YTD amount places you in the bracket. The bracket percent is multiplied by the individual sale.

I need a query that will show me the sales person, the sales amount, the date of the sale, that sales persons YTD per sale.
Example:
Bob $1000 05/02/04 $1000
Bob $2000 05/06/04 $3000
Tom $500 05/01/04 $500
Tom $1000 05/02/04 $1500

I have tried using DSUM and some add-in. They came kinda close but no cigar.

I have tried to have a qry that did a running sum on the totals of each RSM then used a sub qry but that didn't work either.

Please help. Thanks

 

[LittleSmudge]

How about something like

SELECT SalesPerson, SaleDate, Sum(SaleAmount) As DayTotal
FROM tblDataSource
GROUP BY SalesPerson, SaleDate ;


You can then use that string at the recordSource for an Access report which will allow you to display Totals for the Person by Time Period (Eg Week, Month, Year etc ) as well as the daily totals.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[mordja]

dcwave,

You may need to write a function. This will do it. Open a new module and paste this in.


Option Compare Database

Public name As String
Public YTD As Double

Function AddSales(cname As String, sale As Double)

If name = cname Then
YTD = YTD + sale
AddSales = YTD
Else
name = cname
YTD = sale
AddSales = YTD
End If

End Function


Then in a query add an expression that calls the above function with your sales persons name and the salevalue. The data must be sorted by sales person for the above to work.

Mordja

 

[LittleSmudge]

If you just want YearToDate for each person then

SELECT SalesPerson, Sum(SaleAmount) As YTDTotal
FROM tblDataSource
GROUP BY SalesPerson
WHERE Year(SaleDate) = Year(Date());



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

For a running sum:
SELECT A.SalesPerson, A.SalesAmount, A.SalesDate
, (SELECT Sum(B.SalesAmount) FROM tblSales B WHERE B.SalesPerson=A.SalesPerson AND B.SalesDate<=A.SalesDate) As RunningAmount
FROM tblSales A

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

 

[dcwave]

PHV,
Your query for a running sum is what I need. However, I need to run the sum based on a selected year. For example, I have data for 2003 and 2004; when running this query I get a running sum based on all of the records - both 2003 and 2004. I need to be able to select only the records for 2004. I will play around with it some more to see what I come up with. If you have any more suggestions please reply. Thanks

mordja,
The function works too, although, if the same query is run twice with the same criteria then the YTD is continually growing, much like using a static variable. While this might not be a problem, it has potential to be one.

Thank you all for your replies!

 

[PHV]

Try this:
SELECT A.SalesPerson, A.SalesAmount, A.SalesDate
, (SELECT Sum(B.SalesAmount) FROM tblSales B WHERE B.SalesPerson=A.SalesPerson AND B.SalesDate<=A.SalesDate AND Year(B.SalesDate)=Year(A.SalesDate)) As RunningAmount
FROM tblSales A

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

 

[dcwave]

Thanks PH. Thats exactly what I needed. Now I can run a sub query that calculates the correct commissions.