Forum Discussion
Multi Table (Query) Report
I am trying to create an Employee Certification Summary report that uses records from 5 different data tables/queries. The records from separate tables/queries are as follows:
Data # of Records
Technical Examination 1
Eye Exam (medical) 1
Formal Education 1
Past Experience 1
Completed Training Classes 8
Because there are 8 Completed Training Class records for the same employee, my report seems to repeat all of the 1-record data 8 times (creating many printed pages). How can I get all of these records to print on a single page and eliminate repeats of both labels and data?
I need a bit more detail about the other tables and I'm not sure exactly how you wanted the training formatted across the page, but you could do something like this.
Instead of joining the training history as a table in your query with the other tables, you can get the eight training records as columns if you use the TRANSFORM command. That turns it from vertical to horizontal each person's training on one row with all the other info.
Create a query using the training history table. You have to do it in SQL, like this. Save this query and just put it in your query replacing the table.
TRANSFORM First(CompletedTrainingClasses.TrainingDate) AS Training
SELECT CompletedTrainingClasses.EmpID, First(CompletedTrainingClasses.TrainingDesc) AS FirstOfTrainingDesc
FROM CompletedTrainingClasses
GROUP BY CompletedTrainingClasses.EmpID
PIVOT CompletedTrainingClasses.TrainingDesc;Each row becomes a column like below.
ID Exam Educ PastExpTech ExamFirst Second Third Fourth Fifth Sixth Seventh
1 Eye Exam Done Educ Done yes tech 1/1/2020 1/15/2020 1/31/2020 2/14/2020 3/17/2020 4/12/2020 5/12/2020 6/1/2020
6 Replies
I need a bit more detail about the other tables and I'm not sure exactly how you wanted the training formatted across the page, but you could do something like this.
Instead of joining the training history as a table in your query with the other tables, you can get the eight training records as columns if you use the TRANSFORM command. That turns it from vertical to horizontal each person's training on one row with all the other info.
Create a query using the training history table. You have to do it in SQL, like this. Save this query and just put it in your query replacing the table.
TRANSFORM First(CompletedTrainingClasses.TrainingDate) AS Training
SELECT CompletedTrainingClasses.EmpID, First(CompletedTrainingClasses.TrainingDesc) AS FirstOfTrainingDesc
FROM CompletedTrainingClasses
GROUP BY CompletedTrainingClasses.EmpID
PIVOT CompletedTrainingClasses.TrainingDesc;Each row becomes a column like below.
ID Exam Educ PastExpTech ExamFirst Second Third Fourth Fifth Sixth Seventh
1 Eye Exam Done Educ Done yes tech 1/1/2020 1/15/2020 1/31/2020 2/14/2020 3/17/2020 4/12/2020 5/12/2020 6/1/2020 - Do the 8 training classes have a predictable identifier, like training, training, etc?
- They are logged with the following entries:
Training Date
Training Organization
Training Description
Training Hours
Any one of these can repeat from record to record. So, I think the answer to the question is no.
Set the Hide Duplicates property of the relevant controls to Yes.
Thanks for the response HansVogelaar . I've tried this - it hides the duplicate data, but it does not eliminate repeats of the labels (and thus, creates additional pages of report). In my example, the 1 table that has 8 data records (since those records are all different) triggers the production of 8 total pages of report - 7 of them with labels that include no data except the single record from the table with 8 distinct data records. There seems not to be away to put the 8 records all on the first page and eliminate the additional pages (at least, as far as I can see with my limited knowledge).
My latest effort had me embedding the table with 8 records into the report as a subreport. This at least allowed me to get the 8 records on 1 page, but does not eliminate all the additional pages with labels only (no data because duplicates are hidden). Thoughts?
I'd either remove the labels or move them to a group header section.
