Jul 20 2020 10:03 AM
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?
Jul 21 2020 12:19 AM
Set the Hide Duplicates property of the relevant controls to Yes.
Jul 21 2020 07:09 AM
Thanks for the response @Hans Vogelaar . 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?
Jul 21 2020 02:27 PM
I'd either remove the labels or move them to a group header section.
Jul 23 2020 05:05 PM
Jul 24 2020 06:31 AM
Jul 24 2020 10:49 AM
SolutionI 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 |
Jul 24 2020 10:49 AM
SolutionI 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 |