Adding and subtracting totals from 3 seperate Reports

[LaughingBull]

I have 3 reports
Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December

The are all run from the same query with different Between Dates.

The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values

Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.



This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.

Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.

If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.

The way it is now I have to run all 3 report and do the math by hand this way.

Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?

[ItsMe]

I think I understand that the query or queries you use to create the reports are not functional for creating the calculations desired. In cases like this, I will use code in the OnOpen event of the Report that takes a separate trip to the data and does the calculations. If the calculations are complex I will have a separate query retrieve the data. I can execute the separate query via the same code in the OnOpen event of the report or I might be able to JOIN the separate query as a subquery to the Report's existing RecordSource.

In short, if you cannot get it to work in a single trip to the data, you will need to create additional queries. Often times, alias' are helpful in situations like this.