Running Sum in Access Query

I need to create a running total in my access query and from what I've read it says to use the DSUM function but I'm not entirely sure how to set it up. I could use a little help creating the running total. My query is below. The column that I need to create the running total for is the "Grind Hours: ACTHOURS" column. I need it to sum up that column for any dates (WorkWeek) less than the date entered as my criteria date. The "WorkWeeK" column is the actual date field which just shows a normal date when run. The criteria is actually in the qryCellLoadDailySummaryVS1GrindHours query and that's why you don't see that criteria in the query syntax below. Any help would be greatly appreciated.


SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours], qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek;

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
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.

Jim Dettman (EE MVE)President / OwnerCommented:
Are you doing this for a report?   Reports have the running sum functionality built-in.   It's a property of a text control.  Just set it to true.

Jim.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No, this is for a query that shows on a form only.
Jim Dettman (EE MVE)President / OwnerCommented:
I should add that you can do something similar in a form by have a text control with a control source of:

=Sum([<some field>])

 But this would be a total of all the records (and might work).

 But if you truly need a running sum in a form, then what you want is a sub select in your query.  The domain functions should not be used in a query.

Jim.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The form is a continuous form that shows the records in ascending sort based on the date field. And I have it set to only show the top 5 records using the date criteria that is entered. So it will show the first record based on the date entered and then the next 4 dates in consecutive order. So could I use the SUM in the form since I will be totaling all the records? And if that is the route to go how would I set that up on the continuous form? Just add a field to the right of the field I need to sum and have it show as a continuous field? It doesn't have to be in my query so the form route might be the way to go.
Jim Dettman (EE MVE)President / OwnerCommented:
No, you will need a true running sum query then:

SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours],

(Select SUM([ACTHOURS]) From qryCellLoadDailySummaryVS1GrindHours WHERE qryCellLoadDailySummaryVS1GrindHours.[WorkWeek] <= T1.[WorkWeek])  as [GridHoursTotal],

qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours AS T1 INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek;
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you. I am getting a syntax error in the JOIN operation but I can't see where the problem is. It looks correct to me and spelled correct.
Jim Dettman (EE MVE)President / OwnerCommented:
By the way, a DSum() you might find simpler, but it's poor for performance:


SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours],

Dsum("[ACTHOURS]", "qryCellLoadDailySummaryVS1GrindHours", "[WorkWeek] <=  #" & [WorkWeek] & "#") as [GridHoursTotal],

qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours  INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek;


 Note the similarity to the Sub Select.  All the Domain functions are nothing more that SQL Statements contained in a function.   They are intended to be used in places (like a control source) where SQL statements are not allowed.  Since were writing a query, which is basically a SQL statement, there is no need to use them.

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
<< I am getting a syntax error in the JOIN operation but I can't see where the problem is. It looks correct to me and spelled correct.>>

 Let me double check something....

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
Hum...looks like everything is in the right place.    Maybe I didn't copy and past something right.  If your working in the query designer, add this as a column:

GridHoursTotal:(Select SUM([ACTHOURS]) From qryCellLoadDailySummaryVS1GrindHours WHERE qryCellLoadDailySummaryVS1GrindHours.[WorkWeek] <= T1.[WorkWeek])

 then right click on the table qryCellLoadDailySummaryVS1GrindHours, select properties, and change the alias name to T1

Jim.

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
Dale FyeOwner, Dev-Soln LLCCommented:
Keep in mind that you will have the same values for [WorkWeek] in this year as you did last and the year before that.  So, unless your table only includes a single years data, you might want to consider also filtering on the year (your main query may already handle that).

Dale
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok I tried both queries but I am still getting the syntax error in the JOIN statement for the first query. Here is what I have for that query. I cannot get into design view because of the error. I'm stuck in the SQL view because of that error. The column you wanted me to add afterwards isn't in there because I wasn't sure how to add that with the syntax error still happening on this query.

SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours], (Select SUM([ACTHOURS]) From qryCellLoadDailySummaryVS1GrindHours WHERE qryCellLoadDailySummaryVS1GrindHours.[WorkWeek] <= T1.[WorkWeek])  as [GridHoursTotal],qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours AS T1 INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek; 

Open in new window


The query with the DSUM in it is showing a #ERROR for the GrindHoursTotal results when I run the query with the DSUM in it. Here is that query:


SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours], DSum("[ACTHOURS]","qryCellLoadDailySummaryVS1GrindHours","[WorkWeek] <=  #" & [qryCellLoadDailySummaryVS1GrindHours].[WorkWeek] & "#") AS GrindHoursTotal, qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek;

Open in new window

Jim Dettman (EE MVE)President / OwnerCommented:
Paste this:

SELECT qryCellLoadDailySummaryVS1Capacity.WorkWeek, qryCellLoadDailySummaryVS1Capacity.GTHours AS Capacity, qryCellLoadDailySummaryVS1GrindHours.ACTHOURS AS [Grind Hours], qryCellLoadDailySummaryVS1Capacity.MonWeek, qryCellLoadDailySummaryVS1Capacity.LastFriday, qryCellLoadDailySummaryVS1Capacity.TotalDays, qryCellLoadDailySummaryVS1Capacity.TotalCapacity, IIf([MonWeek]>Date()-Weekday(Date())+2,[TotalCapacity]-[Grind Hours],[TotalCapacity]-([Grind Hours])) AS HoursAvailable, IIf([MonWeek]>Date()-Weekday(Date())+2,[Grind Hours]/[TotalCapacity],([Grind Hours])/[TotalCapacity]) AS LoadPercent
FROM qryCellLoadDailySummaryVS1GrindHours INNER JOIN qryCellLoadDailySummaryVS1Capacity ON qryCellLoadDailySummaryVS1GrindHours.WorkWeek = qryCellLoadDailySummaryVS1Capacity.WorkWeek;

which is your original query that you posted above into the SQL view, then switch to design view and do what I said in the comment before this.

Jim.
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.