Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Running Sum in Access Query

Posted on 2016-08-17
12
Medium Priority
?
88 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 58
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 58
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 58

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 58

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 58
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 58

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 58
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

876 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