SOLVED

Multi Table (Query) Report

Copper Contributor

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?

 

6 Replies

@ianradcliffe 

Set the Hide Duplicates property of the relevant controls to Yes.

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?

@ianradcliffe 

I'd either remove the labels or move them to a group header section.

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.
best response confirmed by ThereseSolimeno (Microsoft)
Solution

@ianradcliffe 

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

1Eye Exam DoneEduc Doneyestech1/1/20201/15/20201/31/20202/14/20203/17/20204/12/20205/12/20206/1/2020
1 best response

Accepted Solutions
best response confirmed by ThereseSolimeno (Microsoft)
Solution

@ianradcliffe 

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

1Eye Exam DoneEduc Doneyestech1/1/20201/15/20201/31/20202/14/20203/17/20204/12/20205/12/20206/1/2020

View solution in original post