Running total in a query

[RBE]

I have one more question. How can I get a running sum into a query. I thought about creating a function to do the calculation but I can't think of a way to get it to work in a query. or even how to get it to work at all. any suggestions. Thanks
RBE

 

[tlbroadbent]

Check thread181-134223 and the following MS KB articles, "How to Create Running Totals in a Query."

Access 97:

http://support.microsoft.com/support/kb/articles/Q138/9/11.asp

Access 2000:

http://support.microsoft.com/support/kb/articles/Q208/7/14.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[RBE]

Hi, I went to the link and I am very greatful for the lead. I am however haveing problems getting the dsum to work in my query. can you take a look at it and see if you can see a big error sticking out.

Format(DSum("[gallons received]","[tenders table]","[destination terminal]=" & [product code]![destination terminal] & "&quot,"0,000&quot

[destination terminal] on both tables is text first one is on tenders table.
When I try to run the query it comes back with this error

"You tried to execute a query that does not include the specified expression 'Format(DSum("[gallons received]","[tenders table]","[destination terminal]=" & [product code]![destination terminal] & "&quot,"0,000&quot' as part of an aggregate function."

What does this mean? and How do I fix it?

 

[tlbroadbent]

You must have a Group By clause in the query. When you have a Group By clause, every column in the Select list must be included in the clause or in an aggregate function. DSum is a domain aggreagate function that operates independently of the query in which you've embedded it. Most likely, you'll need to use the aggregate function SUM rather than DSum. However, in order to give more specific feedback, I'll need to see the entire query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.

 

[RBE]

There is 53 lines of code in this query, I hesitate to post all of that. This is a query for a report. However I do have group bys in the query and all fields that are in the dsum are in the query with groupby totals. The ms faq page sugggested that I use the dsum to create a running total in the query, however I need it to run by group of locations. the destination terminal field is the only field that has its commanality with both the tables. I can post all 53 lines but you would have to weed through alot of non-essintial stuff to get to the problem. Happy to answer all questions though.

 

[tlbroadbent]

Do you want to Email it to me? If so, send it to tlbroadbent@hotmail.com. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.