Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 24 Posts

·
Registered
Joined
·
63 Posts
Discussion Starter · #1 ·
Hi there,

Can you give me some idea on how to do this...

I am creating a Training Management solution. For this some of the training modules they need requalification after x time. (This time is stored in the database, but is currently stored as (6 months, annually or 3 years) (should this be stored as the number of days?).

So the training table looks like this:

TrainingCode - Primary Key
Traning Name
Requires refresher? - List (6 months, annually, 3 years)
Mandatory? - Yes/No

And I have another table which links a colleague to a training module with a date they trained which looks like this:

ID - Primary Key
Training Name - List from Traning table
Colleague Name - List from Colleagues table
Date Trained
Trainer - List from Colleagues Table

Nowww... what I can't get my head around is:

a) How to create a report to show a list of all colleagues that are overdue requalification training

b) How to create a report to show a list of all colleagues that are due refresher training in the next 60 days

c) How to create the queries to run both of the above reports

d) How to print a list of all mandatory training that is incomplete...
That is a list of all colleagues mandatory training for what there is no record in the ColleagueTraining table.. I already have a list of all mandatory training linked to a job role in the ManTraining and Colleagues tables respectivley.

In addition, I need to be able to limit the details put into the table by the following factors:
- Colleagues should not be allowed to enter more than one training session, unless the session requires refresher training
- Refresher training should be stored in addition to previous traning
 

·
Registered
Joined
·
19,896 Posts
As long as you do not need the Retraining to an exact day the 6 Month, Annual & 3 years is OK.
But why not translate that in to a future Date?
The queries that you require are reasonably straight forward, even more straight forward if you use the future date as suggested.
I have a couple of reservations about your Table Collegue/Training design, you shouldn't store names in it just the TrainingID and the CollegueID.
If you could create an Access 2000-2003 database and Import your tables (with some dummy data - no personal details) and then post a zipped copy on here I can assist you with the queries.
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #3 ·
Hi,

Attached is a copy of the file with various traning names in them. Just myself and a test colleague in the colleagues table.

Feel free to play around with it, as I dare say it's not the best in the world.. im not a database guy haha, i can do the reports, so its just the backend im worried with

Thanks :)
 

Attachments

·
Registered
Joined
·
19,896 Posts
Mike, I have created 4 new Queries, the first returns the Last Date a person is Trained on a subject and is called "traincoll Lastdate".
The others provide a list of Overdue Training (training Overdue), Training Required in the next 60 days (training Required) and last Mandatory Training Required (Mandatory).
To demonstrate the last you have been promoted and have been trained for "Managing Store Food Safety".
I am not sure about the other requests, perhaps you could explain a bit more about them.
I have not modified any Relationships.
 

Attachments

·
Registered
Joined
·
63 Posts
Discussion Starter · #6 ·
OPB,

Thanks for that!!!! It was the MAX function i was looking for!!

The only other thing I am unsure of how to do is:

My plan was for the table trainjob to link a job to required training. Training can either be mandatory or non mandatory, however needs to be assigned to specific job roles.

Thanks for all your help OPB
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #7 ·
Alsooo... a small flaw I've only just thought of.

When you requalify for something (ie first aid - every 3 years), and you are overdue, then your name appears on the Overdue training query.. however, when you are retrained the old name still appears.. is there any method of archiving this information?

Sorry.. I am such an access newb it is untrue :)
 

·
Registered
Joined
·
19,896 Posts
Can you repost the database demonstrating what you mean by "when you are retrained the old name still appears".
The trainjob is the right way to go, you should store the JobID and the trainingID in there.
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #9 ·
Attached is an updated database showing what i mean.

If you see the traincoll LastDate query.. you will see i refreshed my First Aid training sometime in 2009, however if you check the training Overdue query, you will see that my 2004 training still shows as being overdue.

Thanks
 

Attachments

·
Registered
Joined
·
63 Posts
Discussion Starter · #11 ·
Baically.. if you add me in the training table for having done a first aid course today, i will still appear on the Training Overdue query as the 2004 course has gone past the 3 years refresher boundary..
 

·
Registered
Joined
·
19,896 Posts
You have already entered your First Aid training on the 30/08/2009 and First Aid doesn't come up on the overdue list.
Perhaps when you checked it you hadn't closed the table to register the new date.
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #13 ·
Sorry.. my mistake.. i think i was running the query without saving the table modifications :). Sorry.. as i said.. access newb :)
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #15 ·
Sorry.. final one OPB.

Current plan of action is to create a query, which compiles everything that is required for a job role, and compares it against training an employee has taken, and then show me what is missing.. and hence show me what employee has training outstanding.

Just to clarify mandatory training is required for health and safety, or legally.. however all training for a job role should be shown, whether mandatory or not
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #17 ·
Hey OPB,

I've been trying various different approaches.. all of which have yielded nothing, i think it may just be an access feature that I am unaware of.. is there a way to compare a list from one table, against details in another table, and show the data which is NOT in both tables? (and so show me what is missing from the training record)
 

·
Registered
Joined
·
19,896 Posts
Yes there is, include both tables in the query, but not related.
In the Criteria row of the Training table enter
<> othertable.fieldname
 

·
Registered
Joined
·
63 Posts
Discussion Starter · #19 ·
OPB,

Attached is the latest version.. please can you help, as I think im being incredibly thick.

The query in question is TEST training required, its just thrown up random results..

I know im being thick, but i cant see where.

The original intention of the query was to show a list of all training required for a job role, compares it to training recieved by a colleague and then shows what training still needs to be completed for a job.
 
1 - 20 of 24 Posts
Status
Not open for further replies.
Top