Running Total

I have a table called tblpaysummary and a tblpayrollpaying.  I have query that shows me all the records from tblpaysummary where paydate = tblpayrollpaying.  In addition, in the query, I would like to create a running total of all the grossprofits plus the grossprofit by salemen. I have attached a database with my tables and query.
question.accdb
cres1121Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GozrehCommented:
Check this out
question.accdb
0
cres1121Author Commented:
I need it to add up for the cumulative total everything in the tblpaysummary table versus just what is returned in the query.  So the cumulative total is from the beginning of the year.  As a side when you use dsum does all your criteria have to come from the tblpaysummary or can you reference other fields in your query.
0
GozrehCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cres1121Author Commented:
Thanks I have read that many times.  To bad it makes very little sense to me.  I will keep trying
0
Jeffrey CoachmanMIS LiasonCommented:
Then does it *have* to be done in a query?
This is ridiculously easy to do in a Report (using the running sum property of a control...)
imagequestion.accdb
0
cres1121Author Commented:
I so appreciate it needs to be done in query because I have to determine a commission percentage based on the total grossprofit.  We have three different levels and I want to have the query pick the rate.  >300000 is 12% >600000 is 15% and over 600000 is 18%.
0
Jeffrey CoachmanMIS LiasonCommented:
I have to determine a commission percentage based on the total grossprofit.  We have three different levels and I want to have the query pick the rate.  >300000 is 12% >600000 is 15% and over 600000 is 18%.
?
This was not what you stated in your original post.

You asked for this:
I would like to create a running total of all the grossprofits plus the grossprofit by salemen.
...this is what my report displays.
0
cres1121Author Commented:
Well I can do the logic if I get the running total in my query.  That is why I can't do it in a report.
0
GozrehCommented:
CCur(DSum("Grossprofit","tblpaysummary","Salemen='" & [Salemen] & "' AND [Pay Date]=#" & [Pay Date] & "# AND Paysummary<=" & [Paysummary]))

The running sum is based on the "Paysummary<=" & [Paysummary] it sums the record if its less than or equal to the current record
the other criterias you can change them whatever to you want,
like if you want to calculate the sum from beginning of the year you can do it like this:
"Paysummary<=" & [Paysummary] & " AND Salemen='" & [Salemen] & "' AND Year([Pay Date])=" & Year([Pay Date])

Open in new window

This will give you the YearToDate total for each record.
question.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cres1121Author Commented:
This is exactly what I needed. I so appreciate the help now I have to make it work in my enviorment
0
cres1121Author Commented:
I am not getting it.. I get a #error what does that mean other than it is wrong.  I had to do it from a query versus a table but so frustrating.
0
GozrehCommented:
What is error you are getting ?
0
GozrehCommented:
Show me the criteria you using in the dsum statement
0
cres1121Author Commented:
I took it one variable at a time and finally got more then #error... Thanks again..  Not done but one step I might be back
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.