Training Tracker

[fawnlemur]

Hi everyone


I'm a MS access beginner,

I want to make a database that would track which staff have done a certain training course and which staff still need to do it.

I have 4 excel sheets:

Sheet1 - location - colleague full name - colleague ID
Sheet2 - Colleague forename - Colleague Surname - Manager - Senior Manager - Status - Rank - Colleague ID
Sheet3 - Colleague Forename - Colleague Surname - Course ID - Course Name- Colleague ID - Completion Date - Formula (=CourseID&ColleagueID)
Sheet4 - Course ID - Course Name


Sheet2 and Sheet3 are extracted from other databases (not access and I have no access to edit them, just export the data). I can edit the data after exporting it (but I'd like to keep it the same.)

I found it easier to add the data to excel and then import it to access (and using save import it's easy to update it) - if there is a better way please let me know


I want to be able to view the data so it would show like this

Location	Colleague full name	Manager	Senior Manager	Status	Rank	Colleague ID	Course1	Course2	Course3
London	Adam Smith	Vanessa Rogers	Mike Schofeild	Permanent	1	1234	25/04/2018		23/02/2018
New York	Pete Jones	John Adams	Mike Schofeild	Temp	1	1235			22/04/2018
New York	John Adams	John Adams	Mike Schofeild	Holiday	2	1232
New York	Mike Schofeild	Mike Schofeild	Mike Schofeild	Permanent	3	1231	23/02/2018	23/02/2018	23/02/2018
London	Vanessa Rogers	Vanessa Rogers	Mike Schofeild	Parental Leave	2	1236	22/01/2018

I made a table for each sheet, and connected them in a relationships (colleague ID and Course ID) and ran the query wizards, I'm not sure if I set up the tables incorrectly or the relationship, but this is what I came up with.

Location	Colleague full name	Manager	Senior Manager	Status	Rank	Colleague ID	Course	Completion Date
London	Adam Smith	Vanessa Rogers	Mike Schofeild	Permanent	1	1234	Course1	25/05/2018
New York	Pete Jones	John Adams	Mike Schofeild	Temp	1	1235	Course2	23/04/2018
New York	John Adams	John Adams	Mike Schofeild	Holiday	2	1232	Course3	22/01/2018
New York	Mike Schofeild	Mike Schofeild	Mike Schofeild	Permanent	3	1231	Course2	23/02/2018
London	Vanessa Rogers	Vanessa Rogers	Mike Schofeild	Parental Leave	2	1236	Course1	22/02/2018
London	Adam Smith	Vanessa Rogers	Mike Schofeild	Permanent	1	1234	Course2	23/04/2018

The colleague data and then list the course and the completion date in a list. it doesn't show who hasn't completed the course and I want the format of the above table.



I'm new to access so there is a lot I don't know. any tips on how I can Achieve this.

Thank You

[isladogs]

There have been several posts on this topic recently on various forums.
Am I correct in thinking this is a school or college project?
If so, that's fine but it affects the nature of the guidance you should receive.

For now, I'll just say look up normalisation of data.
Databases should not have fields called Course1/2/3.
Doing so is trying to use Access as a spreadsheet

However if you are saying that should be the result of a query, you need to use a crosstab query for which Access provides a wizard.

[fawnlemur]

Thank you this really helps

[isladogs]

You're welcome. So is it a school/college project?

[fawnlemur]

its a college project, I'm still working on it