I need Help with a DSUM function in an Access query 1

[anaej]

I'm attempting to create a running sum for a percent field I calculated but I can't get the running sum to work and I'm not sure what I'm doing wrong. The fields in my total query are year(group by), month(group by), category (group by), supplier (group by), total cost (sum), total wt (sum), and percent (sum). I want a running sum for each category by month and year. I have attempted a variety of combinations of criteria but I keep getting a total for the criteria instead of a running total. I tried first to just get a running sum by year but I couldn't get my formula to work. What am I doing wrong?

Total percent: DSum("percent","Summary Log cost info 4","year <=" & [Year] & " ")

 

[lespaul]

instead of showing us minimal information from the Query Design Grid, please switch to SQL view and post the entire query.

thanks
leslie

 

[anaej]

SELECT [Summary Log cost info 4].Year, [Summary Log cost info 4].Month, [Summary Log cost info 4].[Rec Location], [Summary Log cost info 4].[tps cat] AS Category, Left([po_no],5) AS [Supplier No], Sum([Summary Log cost info 4].[Total Cost]) AS [SumOfTotal Cost], Sum([Summary Log cost info 4].[Total Net Wt]) AS [SumOfTotal Net Wt], Sum([Summary Log cost info 4].[Total Stems]) AS [SumOfTotal Stems], [Summary Log cost info 4].[Tons per stems], Sum([Summary Log cost info 4].percent) AS SumOfpercent, DSum("percent","Summary Log cost info 4","year <=" & [Year] & " ") AS [Total percent]
FROM [Summary Log cost info 4]
GROUP BY [Summary Log cost info 4].Year, [Summary Log cost info 4].Month, [Summary Log cost info 4].[Rec Location], [Summary Log cost info 4].[tps cat], Left([po_no],5), [Summary Log cost info 4].[Tons per stems]
ORDER BY [Summary Log cost info 4].Year, [Summary Log cost info 4].Month, [Summary Log cost info 4].[Rec Location], [Summary Log cost info 4].[tps cat], Sum([Summary Log cost info 4].[Total Cost]);

Thanks for any help you could provide!

 

[dhookom]

If you want to calculate a running sum "for each category by month and year" you would need to include these fields in the WHERE clause of DSum() using "=" and some other unique field with the "<=".

Total percent: DSum("percent","Summary Log cost info 4","year =" & [Year] & " AND Month =" & [Month] & ...")

Do you really need to do this in the query? If your result is displayed in a report, there is a MUCH EASIER solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[anaej]

I attempted to create a running sum including the other fields and I could not get the running sum to calculate correctly. So when I couldn't get the running sum to work using 3 criteria, I attempted to create the query using only 1 criteria in order to try to pinpoint my problem and the query still did not work. I know if I can figure out what I'm doing wrong with the one criteria, I can fix all 3 criteria so they will work.

To answer your question if I really need it in a query, I need to build an additional query to limit the data by information in the running sum. I have created a report with the running sum but I have been unsuccessful in limiting the inoformation and creating total information based on the running sum in the report. I am open to suggestions. What I really want to do is calculate the total cost and total net wt of the highest 10% of costs by category.

Thank you in advance for any assistance you can provide.

 

[dhookom]

If I were you, I would open Northwind and attempt to create a running sum of freight charges by Customer in the Orders table. This would be much simpler and you could then apply what you learned to your current project. If you had trouble, almost everyone can help you with Northwind since we all have it installed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[anaej]

I did just what you suggested and I was able to make the running sum work in Northwind. That's why I posted my problem here. I applied the same knowledge from Norhtwind and it didn't work. So I thought maybe I was doing something obvious that someone else would pick up on. When I get a value returned, the value is the same for each detail row instead of a running sum.

 

[dhookom]

Your Dsum() posted here used:
DSum("percent","Summary Log cost info 4","year <=" & [Year] & " ")
I would like to see what you used in Northwind since your year field shouldn't be used with <=. I would expect that you want a running sum through a year and not where the year in the DSum() is less then or equal to the year in the query.

Maybe if you took the time to type a sampling of about 10-15 records with the significant fields and values of raw data with the expected running sum column values. We might then be able to provide greater assistance.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[anaej]

I used the example I found when I did a search on running sums on google.com. The DSum() I used was

RunTot: DSum("Freight","Orders","DatePart('m',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',[OrderDate])<=" & [AYear] & "")

 

[dhookom]

It isn't clear whether the running sum starts over at any change of field value or runs over all records. The example you site is over all records. If you want over groups of records, you need to tell us (and tell your DSum()).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]