DSum running total ?

[Guest]

Hello,
Please help me out, I have spent a day struggling with this problem.

SalesRep Customer Date Amount RunningTotal
Rep 1 Cust A. 09/20 500 500
Rep 2 Cust b. 09/21 10000 10000
Rep 1 Cust C. 09/22 5050 5550
Rep 2 Cust D. 09/23 3040
103040
Rep 2 Cust E. 09/24 50000
153050

My goal is to add the rep's total in the running total so that everytime the
rep makes a sale the running total is recalculated. The comision that the
rep is paid is based on how much has been sold. So I need to see a total for
each time a sale is made.

I have been trying to use DSum([Amount],"TBLE","[TBLE]![Date] & "<[Date] &
"")
and I have not been recieving the results that you see in the RunningTotal
column. Can someone please suggest what I am doing wrong or a particular
course of action that I should take.

Can you also suggest a website for syntax, when to use [] and when to use
"", #,!...

Thanks in Advance!

 

[Roger Carlson]

RunningTotal: DSum("Amount","TBLE","[Date] <=#" & [Date]&"#")

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RunningSumInQuery.mdb" which illustrates how to do this a
couple of different ways.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Van T. Dinh]

Try the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714/en-us

 

[Marshall Barton]

Please help me out, I have spent a day struggling with this problem.

SalesRep Customer Date Amount RunningTotal
Rep 1 Cust A. 09/20 500 500
Rep 2 Cust b. 09/21 10000 10000
Rep 1 Cust C. 09/22 5050 5550
Rep 2 Cust D. 09/23 3040
103040
Rep 2 Cust E. 09/24 50000
153050

My goal is to add the rep's total in the running total so that everytime the
rep makes a sale the running total is recalculated. The comision that the
rep is paid is based on how much has been sold. So I need to see a total for
each time a sale is made.

I have been trying to use DSum([Amount],"TBLE","[TBLE]![Date] & "<[Date] &
"")
and I have not been recieving the results that you see in the RunningTotal
column. Can someone please suggest what I am doing wrong or a particular
course of action that I should take.

Can you also suggest a website for syntax, when to use [] and when to use
"", #,!...

If SalesRep is a numeric type field:
DSum([Amount],"TBLE",
"[Date] < " & Format([Date], "\#m\/d\/yyyy\#")
& " AND SalesRep = " & SalesRep)

If SalesRep is a Text field:
DSum([Amount],"TBLE",
"[Date] < " & Format([Date], "\#m\/d\/yyyy\#")
& " AND SalesRep = """ & SalesRep & """")

This kind of query can be very slow when there are a
significant number of records in the table. If you are
going to use the results of this query in a report, be aware
that you drop this calculation from the query anf use a
RunningSum text box in a SalesRep group without the
performance penalty.

 

[Guest]

Thanks to everyone it worked

Please help me out, I have spent a day struggling with this problem.

SalesRep Customer Date Amount RunningTotal
Rep 1 Cust A. 09/20 500 500
Rep 2 Cust b. 09/21 10000 10000
Rep 1 Cust C. 09/22 5050 5550
Rep 2 Cust D. 09/23 3040
103040
Rep 2 Cust E. 09/24 50000
153050

My goal is to add the rep's total in the running total so that everytime the
rep makes a sale the running total is recalculated. The comision that the
rep is paid is based on how much has been sold. So I need to see a total for
each time a sale is made.

I have been trying to use DSum([Amount],"TBLE","[TBLE]![Date] & "<[Date] &
"")
and I have not been recieving the results that you see in the RunningTotal
column. Can someone please suggest what I am doing wrong or a particular
course of action that I should take.

Can you also suggest a website for syntax, when to use [] and when to use
"", #,!...

If SalesRep is a numeric type field:
DSum([Amount],"TBLE",
"[Date] < " & Format([Date], "\#m\/d\/yyyy\#")
& " AND SalesRep = " & SalesRep)

If SalesRep is a Text field:
DSum([Amount],"TBLE",
"[Date] < " & Format([Date], "\#m\/d\/yyyy\#")
& " AND SalesRep = """ & SalesRep & """")

This kind of query can be very slow when there are a
significant number of records in the table. If you are
going to use the results of this query in a report, be aware
that you drop this calculation from the query anf use a
RunningSum text box in a SalesRep group without the
performance penalty.