Using MS Access/Cumulative values



I have a table that contains:
- Participant name
- Beginning date
- Ending date
- Value

I need to create a query/report that displays by participant:
- Participant name
- Beginning date
- Ending date
- Value
- Cumulative value per quarter
- cumulative value till today

Thank you.

ANSWER: Hi Maya,

This solution applies to MS-Access Ver 2010.

-First create a Query which has all the required fields
-In the new query have the following parameter entered in your Beginning date  field >=[Beginning date] and in the Ending date field enter the parameter <=[Ending date]
-Save your new query
-Create a new Report based on this query using the Report Wizard
-The report wizard will prompt you to Group by a field name, in your case choose "Participant name"
-The report wizard will then prompt you to create total's for the field you want totaled
-At that time choose "Value" as the field, and use sum to summarized the totals and not count
-When you Preview your report you will be asked to enter the "Beginning date" and the "Ending date"
-Once the parameter values have been entered check the totals on the Value field. They will show you cumulative total[s] for each participant between the dates selected

Hope this helps you

Best regards
Manish Batola

---------- FOLLOW-UP ----------

QUESTION: Dear Manish,
Many thanks for your prompt reply.
I tried to follow your instruction but, after grouping by "Participant", the report wizard didn't ask me to create totals. It skips to the sorting window.
Do you have any suggestions about what is wrong?

Thank you very much

Summary Option Screenshot
Summary Option Screens  
ANSWER: Dear Maya,

The Totals option is embedded in the "Summary Options" button. Please check the attachedscreenshot I have sent you with this email.
When you click on the "Summary Options" button in the Report Wizard you should see a screenshot like what I have sent you. Out there you need to sum or count the values.

Best regards

---------- FOLLOW-UP ----------

QUESTION: Dear Manish,
Your help was priceless to me.
I still have one issue: By entering the "Beginning date" and "Ending date" parameters I can display the individual dose but what I need is that the cumulative dose doesn't consider these parameters and displays the total cumulative dose from the beginning till present.

All the best

Hi Maya,
I'm happy the previous solution worked for you, but with this new request I'm slightly confused

"what I need is that the cumulative dose doesn't consider these parameters and displays the total cumulative dose from the beginning till present."

- In this case you may probably need to create one more grouping for the "Participant name" field.

Or if possible can you sketch me a diagram as to what you would like to see IN ACTUAL on your report so I can get a clear vision.
Also you can write to me directly on anytime.

Best regards

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Manish Batola


ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.


29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Worked in various multinationals catering from Aerospace, Oil & Gas etc.

-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 All rights reserved.