Solved Training Matrix

[Kayleigh]

I'm struggling with creating a query to summarise the data in the format specified by the client.
They would like a table such as below:

Staff ID	Training Category & Title of Training - all statutory trainings as columns
1	Date of most recent training
2	or "No" if null
3	...

The data is in a database.
All staff are to appear once in the left-hand column and all statutory training (if date not expired) are to appear as the columns. Note several trainings may be recorded for each category so only the latest date should be displayed.

What's the best way to create this?

 

[arnelgp]

see query4.
note: no record on staff table so cannot join it in query.

 

[mike60smart]

I'm struggling with creating a query to summarise the data in the format specified by the client.
They would like a table such as below:

Staff ID	Training Category & Title of Training - all statutory trainings as columns
1	Date of most recent training
2	or "No" if null
3	...

The data is in a database.
All staff are to appear once in the left-hand column and all statutory training (if date not expired) are to appear as the columns. Note several trainings may be recorded for each category so only the latest date should be displayed.

What's the best way to create this?

The following Report View is one way of displaying the data.

 

[Kayleigh]

@arnelgp - Thanks for that sample. I have now filled in the tblStaff with fictitious names.
Also I only want one column for each category (to decrease number of empty spaces in final result) so each category be joined together - I have added some more info to the training table to assist.

@mike60smart - I like the report view. Although it includes a lot of detail, so maybe I can offer 'display more detail' to show this option. Can you explain in more detail how this was achieved?

 

[mike60smart]

Hi

It is just a report based on query1

See the attached.