Query to Summarize Data

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I have a database which tracks training done by staff and I need to generate monthly reports based on training done.

I need to produce a monthly report which details the number of courses in that month, the hours of training and the number of employees trained.

Here is my problem!

Let's say 10 employees attended 10 different courses in January with a duration of 1 hour each. For my report, I need:

No of Sessions 10
No of Hours 100
No of Employees Trained 10

So in essence, even though the employee may have attended a number of courses during the month, that employee needs to be counted only once.

I have tried several queries to no avail and have been manually counting the employees. The problem is that for October, I would have over 200 individual employees attending training and it is a bit tedious having to count them every month.

Is there any way that I can create a query to give me this info?

Any help is greatly appreciated.

Michelle
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

SRienstra

New Member
Joined
Dec 3, 2009
Messages
14
There may be a better way depending on your table(s), but I assume you can pull all the relevant fields into a select query (SeminarID, EmployeeID, SeminarDate, SeminarHours) for the below to work. Substitute your own field names, obviously.
Query 1: SeminarGrouping (Select from Seminar Attendence table/query)
SeminarID (Group by)
Month([SeminarDate]) (Group by)

Query 2: SeminarCount (select from SeminarGrouping query)
Month (Group by)
SeminarCount: Count(1) (Expression)

Repeat the above, substituting EmployeeID for SeminarID

Summary Query: Select from Seminar Attendance, SeminarCount and EmployeeCount without any links between them
Month([SeminarDate]) (Group by)
SeminarCount (Group by)
SeminarHours (Sum)
EmployeeCount (Group by)
Criteria:[SeminarCount].[Month] = Month([SeminarDate])
[EmployeeCount].[Month] = Month([SeminarDate])


You can make this a step easier by having the source query with all Seminar Attendance include a column for SeminarMonth (And presumably year, depending on how often you clear the source table) rather than SeminarDate, which will allow linking in the final query instead of the criteria. Alternatively, you could limit the source query to a single month, which would remove the need for linking and criteria in the final query.
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
Thanks for your reply.

Your suggestions worked up until the Summary Query.

I will just explain what I have done using my field and query names.

I created a query called qryCourseGroupingDetails which would be the first step you mentioned. In this query I have the ClassID, EmployeeID, CourseDate and Durations.

I then created a query called qryCourseGrouping. In this query I have ClassID, Year and Month (used the DatePart function to separate the years and months), grouping on all 3 fields.

I have a query called qryCourseGroupingCount which pulls its data from qryCourseGrouping. This query contains the Year, Month and ClassID fields. It is grouped by the Year and Month and counts the ClassID and I used TotalCourses as the alias for that field. I checked and the results of this query are correct.

I have a query called qryCourseGroupingEmployees which pulls its data from qryCourseGroupingDetails. In this query I have the Year, Month and EmployeeID, grouping on all 3 fields.

The last query is called qryCourseGroupingEmployeesCount with the Year, Month and EmployeeID fields. It is grouped by Year and Month and counts the EmployeeID (TotalEmployees is the alias). This information is also correct when I do a manual check.

I believe that I have lost you at this point with regards the summary query.

My summary query is called qryCourseGroupingTotals with the qryCourseGroupingCount and qryCourseGroupingEmployeesCount queries. I have added Year, Month, TotalCourses and TotalEmployees to the query grid and grouping on the first 2 fields. I tried summing and counting the TotalCourses and TotalEmployees fields and got incorrect answers in both instances.

I hope that you are able to follow what I have done and help me with the problem/s that I am making.

Thanks very much for your help.

Michelle
 
Last edited:

SRienstra

New Member
Joined
Dec 3, 2009
Messages
14
When you have multiple tables in a query without any links, Access will perform a "Cross Join", which means that it will evaluate every possible combination.

The simplest solution is to change qryCourseGroupingDetails to have Year and Month instead of CourseDate. With that change, you can link on these fields in the summary step and avoid the (annoyingly complex) criteria.

Also, add qryCourseGroupingDetails to the summary query, linking to the others on year and month. This way you can add a field to sum the duration.

Use Group By for TotalCourses and TotalEmployees rather than Sum or Count, and you should get the correct results.

My apologies for leaving the last step so confusing in my original post.
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403

ADVERTISEMENT

Thanks very much for your help.

My query is correct now.
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
Hi!

I actually have an error with my duration.

A course is sometimes delivered on multiple days and the way I have it set up is:

There is a ClassID which is unique to every training session.
There is also a RecordID which links the Duration, Employees and Course Details.

If a course is conducted across multiple days I am now only getting a total for the 1st day as opposed to a sum of all the hours.

I tried differnet queries for several hours but cannot find a solution.

Please help!
Thanks
 

SRienstra

New Member
Joined
Dec 3, 2009
Messages
14

ADVERTISEMENT

In qryCourseGroupingDetails, how are you pulling date and duration? Is duration available at the day level rather than course description level? The summary should be accurate provided that each employee/class/day combination is represented in qryCourseGroupingDetails with the appropriate duration for that row.

Side note: if a course runs over month end, how do you want it counted in the final report? (Count course and employees in both months and duration in the month it occurred, or count it all in the month the course started or ended)
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
In qryCourseGroupingDetails, I have ClassID, EmployeeID, Year, Month, Duration, RecordID and Course Date.

I have figured out that problem and have gotten another.

A course is sometimes facilitated by more than 1 Facilitator. In that event, I have a button on my form which duplicates the data and I simply have to change the Facilitator's name. That form contains the Course Details (Date, Location, Course Name, Record ID, Course Segment, Methodology). It also contains 2 subforms; 1 for the Duration and 1 for the Employees. The RecordID on the main form and sub forms are the same. The ClassID is unique to every class.

If a course is facilitated by more than 1 Facilitator it is still 1 course. So in essence, if I have a 1 day course, facilitated by 3 person for a duration of 8 hours, I need to see 8 hours for that class duration and not 24 as I am getting now.

I will continue working with it to see if I can find the error.

I appreciate all of your assitance.

Michelle
 

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I was able to get my problem solved. I used qryCourseGroupingDetails and extracted ClassID, Year, Month and Duration and grouped on all the fields and named that qryTrainingHistoryDuration. I then created qryTrainingHistory based on qryTrainingHistoryDuration. I used the Year, Month and Duration fields and summed the duration.

I then added that query to the summary query for the duration portion.

This has given me the answers I need as I had manually counted so I assume that it is okay.

Thanks again for your assistance.

Michelle
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top