Solved

Running Sum in Access Query

Posted on 2016-08-17
12
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 500 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 500 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 500 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 48

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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