Access Running Total Query

Errol_Isenberg
Errol_Isenberg used Ask the Experts™
on
Hi, guys.  
I have the query shown in the attached file which produces the displayed results.  
What I want to know is would it be possible to construct a query that will produce four additional fields with running totals for each week for each associate.
In other words, I would like to have fields labeled “Total Reviewed,” “Total Passed,” “Total Failed,” and “Failed Pct” that would be incremented for each week for each adjudicator.  
The row for the first week for Adams, Rashod would have in those fields 1, 1, 0, and 0.00% respectively.  The row for the second week for Adams, Rashod would have in those fields 2, 2, 0, and 0.00% respectively.  The row for the first week for Aleno, Myrta would have in those fields 2, 2, 0, and 0.00% respectively.  The row for the second week for Aleno, Myrta would have in those fields 4, 4, 0, and 0.00% respectively.
Is there a way to construct a query to produce this result?  Otherwise, I would have to export the results of the current query to Excel and manipulate the results in Excel.
Thank you very much for all your help.
Sincerely,
Errol Isenberg
Government Operations Specialist
Access-Running-Total-Query.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
You can join the qualityReviews table to itself using a non-equi join.   Add a second instance of the QualityReviews table.  Access will suffix it with _1. the join will be:

QualityReviews.StaffID = QualityReviews_1.StaffID AND QualityReviews.IssueID <= QualityReviews_1.IssueID

Since the QBE only supports equi-joins ( = ), once you change the join on IssueID to <=, you will no longer be able to view the query in QBE view.  You will have to view it in SQL View.

Change the query to a totals query so it will group by all the original fields.  Select the fields you want the running sums on and change them to Sum rather than Group By.

Keep in mind that totals queries are not updateable so they are only available for viewing.
Hamed NasrRetired IT Professional

Commented:
Upload a sample database, demonstrating the issue. What tables are used, and show output manually in an access table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial