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?

 

[Uncle Gizmo]

I have an example of Stephen covey's time management matrix done in MS Access on my website here:-

Subform – Time Management Matrix Example - Nifty Access

Time Management Matrix This example demonstrates how you can can use "Instances" of a Form multiple times. You place the copies on the main form as "Sub-Forms" It's an interesting way of programming, and with this technique you can quickly develop Forms with exceptional properties and uses with...

www.niftyaccess.com

The main purpose of the example is to demonstrate how you can use a carrier form (main form) which holds the four subform/subreport controls and then use one single form in each subform/subreport control, (one single form utilised four times)
demonstrating how you can use a single form multiple times...

 

[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.