I have put together a simple example of what I am trying to achieve. Each staff member will do 3 types of exercises each day for their trainer. There are two types of reports, a report card for the staff member detailing how many of each exercise they have done that day, month and year. The other report is exactly the same only it is a summary for all staff for each trainer. Both reports use the same query on the table.
The problem is not the standard totals, but the averages per day for the trainer report. For example, the average push ups per day - originally I was going to get the total push ups for that trainer and divide by the number of days in that month/year so far. However, what is being requested is an average of the staff averages. Therefore rather than it be by total days, I have to somehow find the average push ups for each staff member under that trainer and then get the average. I am new to Access so i am not sure how this could be achieved in the current configuration.
I suspect that ppls first reaction would be to say, put both the trainer and staff in the same report. However, since this is just a sample, the actual report that I want to put together has over 200 elements to report one section, such as the staff. Therefore I cant have both staff and trainer summaries in the same report due to the Access limitation of 256 objects.
I have also attached a spreadsheet which hopefully illustrates what I want to achieve, mathematically, more clearly.
Start Free Trial
