Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access 2007 - time related query


(!)

mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
28-Aug-2009, 07:23 PM #1
Question Solved: Access 2007 - time related query
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

Last edited by mikejreading; 28-Aug-2009 at 07:27 PM.. Reason: Missed a bit :-)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
29-Aug-2009, 07:19 AM #2
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.
__________________
OBP
I do not give up easily
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
30-Aug-2009, 01:02 PM #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 :-)
Attached Files
File Type: zip tracker.zip (101.3 KB, 10 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
30-Aug-2009, 01:28 PM #4
Mike, I will take a look and get back to you tomorrow.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
31-Aug-2009, 06:52 AM #5
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.
Attached Files
File Type: zip tracker.zip (42.8 KB, 9 views)
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
31-Aug-2009, 03:39 PM #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
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
31-Aug-2009, 06:34 PM #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 :-)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
01-Sep-2009, 06:33 AM #8
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.
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
01-Sep-2009, 07:58 AM #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
Attached Files
File Type: zip tracker v 0.1.zip (63.8 KB, 8 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
01-Sep-2009, 08:17 AM #10
I only get disability confident on the list.
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
01-Sep-2009, 08:24 AM #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..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
01-Sep-2009, 08:29 AM #12
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.
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
01-Sep-2009, 08:35 AM #13
Sorry.. my mistake.. i think i was running the query without saving the table modifications :-). Sorry.. as i said.. access newb :-)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,591 posts.
 
Join Date: Mar 2005
Location: UK
01-Sep-2009, 08:47 AM #14
That is a little Access quirk that has caught me out dozens of times over the Years
mikejreading's Avatar
mikejreading mikejreading is offline
Computer Specs
Member with 63 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Mid Degree In Computing
01-Sep-2009, 12:43 PM #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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑