?
Solved

Running Sum in Access Query

Posted on 2016-08-17
12
Medium Priority
?
92 Views
Last Modified: 2016-08-18
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

0
Comment
Question by:Lawrence Salvucci
  • 7
  • 4
12 Comments
 
LVL 59
ID: 41759606
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41759612
No, this is for a query that shows on a form only.
0
 
LVL 59
ID: 41759617
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41759630
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
 
LVL 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 41759648
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41759662
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
 
LVL 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 41759675
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
 
LVL 59
ID: 41759685
<< 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
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 41759704
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41759811
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41759867
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
 
LVL 59
ID: 41759929
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question