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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 (Microsoft MVP/ 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.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No, this is for a query that shows on a form only.
0
Jim Dettman (Microsoft MVP/ 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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
0
Jim Dettman (Microsoft MVP/ 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;
0
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.
0
Jim Dettman (Microsoft MVP/ 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.
0
Jim Dettman (Microsoft MVP/ 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.
0
Jim Dettman (Microsoft MVP/ 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.
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
Dale FyeOwner, Developing Solutions 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
0
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

0
Jim Dettman (Microsoft MVP/ 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.
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.