calculations in 3 different reports

[LaughingBull]

I have an attendance table and query that stores data and the query removes duplicates. each year there are 3 reports. Jan to June, July to Dec and Jan to Dec. what I want to calculate is the difference in the total attendance from the 2 6month reports and the one year report. example Mary attends the Jan to June and on that report she count as 1 attendee. and she attends the July to Dec she counts as 1 attendee but for the year report she only counts as 1 attendee. "Now the meetings are once a week and attendance is entered after every meeting so there is a total of 52 entries for Mary and the query removes the duplicates to show only one in each report." If you add the 2 6 month report totals she counts as 2. I want to add the 2 6 month report totals and subtract the total from the year report to show the difference and the difference will be the number of duplicates. My thought is how to store the totals data from each 6 month and the one year report report in a table, then run a report from that table to show the totals and the difference. Now this all has to be done with the click of a cmd button. i could do this myself but I am just the creator of this DB and will not be a user and the users have no idea how to make this happen.


Thank you for your ideas and help

[NTC]

Well given that you state you can do this yourself - - then once that is established what you want to do is set up a macro that will fire the sequence of queries/reports that achieves the goal.

The macro - while something to avoid in most cases - is in fact quite a good tool to set up a sequence of events to fire (i.e. action queries, open report, etc). Save the macro with a name.... and then at your command button you can set it up to fire the macro.

[LaughingBull]

Sorry, I should have stated that i am very rusty with code as it was 20 years ago that I learned Access and I never used macros only code so I need help with either a macro or code to do so.

[June7]

Usually best not to store aggregate data in table. Build queries that do the summations. Build report, possibly with subreports, to display the queries. Do calcs on report for the difference. Writing the summary data to temp table will require more complex VBA.

Not really understanding your calcs. Seems like the 1-year report totals will always be half of the 2 6-month reports summed.

[LaughingBull]

thanks and that is correct but not always half that was just a simple example.