Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-15-2010, 05:47 AM   #1
bdmang83
Registered User
 
Join Date: Jun 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
bdmang83 is on a distinguished road
Training Database Assistance

Hello,

I have been tasked with setting up a database to track employee training. The easy stuff is to track completed trainings by employee. Additionally I need to be able to track the status of required training by both department or job class. My inital table structure is:

tblEmp
pkEmpID
LastName
FirstName
DepttID
JobClassID

tblTraining
pkTrainID
Topic
ReqDept
ReqClass

tblTrainDetails
pkID
fkTrainID
fkEmpID
DtCompleted

Where I am stuck is how to best query for training that has not been completed but is required for an employee based on either department or job class.

I appreciate any suggestions and if I can get this put together will gladly share the db as an example.

Thanks in advance,

Brian

bdmang83 is offline   Reply With Quote
Old 06-15-2010, 06:14 AM   #2
JamesMcS
Keyboard-Chair Interface
 
JamesMcS's Avatar
 
Join Date: Sep 2009
Location: Northamptonshire, UK
Posts: 1,819
Thanks: 14
Thanked 47 Times in 46 Posts
JamesMcS will become famous soon enough
Re: Training Database Assistance

Hmmm if it was me I'd left join tblEmp and tblTraining with DeptID, jobclassID joining to ReqDept and Reqclass, pull down names, reqdept and reqclass and criteria for the last two being null. That will pull back all the unmatched records... is that what you need?
__________________
Cheers

James
If someone's helped you out, don't forget to click on the scales icon in one of their posts and add to their rep.
JamesMcS is offline   Reply With Quote
Old 06-15-2010, 06:43 AM   #3
bdmang83
Registered User
 
Join Date: Jun 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
bdmang83 is on a distinguished road
Re: Training Database Assistance

James,

Thanks for the quick reply. I'll try that and see what happens.

Thanks,

Brian

bdmang83 is offline   Reply With Quote
Old 06-15-2010, 06:50 AM   #4
JamesMcS
Keyboard-Chair Interface
 
JamesMcS's Avatar
 
Join Date: Sep 2009
Location: Northamptonshire, UK
Posts: 1,819
Thanks: 14
Thanked 47 Times in 46 Posts
JamesMcS will become famous soon enough
Re: Training Database Assistance

Good luck!
__________________
Cheers

James
If someone's helped you out, don't forget to click on the scales icon in one of their posts and add to their rep.
JamesMcS is offline   Reply With Quote
Old 01-12-2014, 07:59 PM   #5
miahmacdougal
Newly Registered User
 
Join Date: Jan 2014
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
miahmacdougal is on a distinguished road
Re: Training Database Assistance

Good evening Brian,

Were you able to get your database to work the way you wanted?

Miah
miahmacdougal is offline   Reply With Quote
Old 01-16-2014, 02:58 AM   #6
Old Man Devin
Consul Of Code
 
Old Man Devin's Avatar
 
Join Date: Jan 2014
Location: UK
Posts: 183
Thanks: 7
Thanked 35 Times in 35 Posts
Old Man Devin is on a distinguished road
Re: Training Database Assistance

Quote:
Originally Posted by miahmacdougal View Post
Good evening Brian,

Were you able to get your database to work the way you wanted?

Miah
Bit late to ask this question considering this happened 3.5 years ago now!
Old Man Devin is offline   Reply With Quote
Old 01-16-2014, 05:02 AM   #7
miahmacdougal
Newly Registered User
 
Join Date: Jan 2014
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
miahmacdougal is on a distinguished road
Re: Training Database Assistance

Actually, there is a method to my madness; I am hoping he says yes, then I will ask to see what he has.

I am trying to develop a required/optional training tracking database for multiple positions that have multiple tasks, and I need a way to track and add both the completed time, and a portion of each training time (including safety meetings); once the time hits a set amount (8 hours), it will notify me so that I can issue a training certificate. If they start coming up to a years time without hitting the required 8 hours, then it needs to alert me.

miahmacdougal is offline   Reply With Quote
Old 01-16-2014, 06:30 AM   #8
Old Man Devin
Consul Of Code
 
Old Man Devin's Avatar
 
Join Date: Jan 2014
Location: UK
Posts: 183
Thanks: 7
Thanked 35 Times in 35 Posts
Old Man Devin is on a distinguished road
Re: Training Database Assistance

Very sneaky!

That does sound quite possible to me; if you made some queries that only returned people with TrainingTime>8 hours then you'd have a list of people to certify for example. Similar for the alert, you could have query of people for whom Now-StartDate > 10 months And TrainingTime < 8 Hours.
Old Man Devin is offline   Reply With Quote
Old 02-12-2014, 01:02 PM   #9
Wiz47
Learning by inches ...
 
Wiz47's Avatar
 
Join Date: Nov 2006
Location: Georgia
Posts: 274
Thanks: 5
Thanked 6 Times in 6 Posts
Wiz47 is on a distinguished road
Re: Training Database Assistance

Quote:
Originally Posted by Old Man Devin View Post
Very sneaky!

That does sound quite possible to me; if you made some queries that only returned people with TrainingTime>8 hours then you'd have a list of people to certify for example. Similar for the alert, you could have query of people for whom Now-StartDate > 10 months And TrainingTime < 8 Hours.
I use a query in my training db that selects those that are within 60 days of needed training. In this case EEO which is required annually. I then only send a notice to those that have not been previously notified via a previous report.


Code:
SELECT Employees.*
FROM Employees
WHERE ((([MTEEO]+305)<TrngDate())) AND NotifiedMTEEO = "No"
ORDER BY Div_Branch, LastName, FirstName;
__________________
"Give a man a fish, and feed him for a day. Teach him to fish, and he'll sit on the dock for hours and drink beer."
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Wiz47 is offline   Reply With Quote
Old 02-12-2014, 01:31 PM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Training Database Assistance

Quote:
I am trying to develop a required/optional training tracking database for multiple positions that have multiple tasks,..
When you deal with training for Positions do you consider the people within those Positions?

People have Training; Positions can have Mandatory Training requirements; Person can be in a Position where Person doesn't have requisite Training...

Attached is jpg used with another post where the above issue was key. This may not be relevant to your situation, but I offer it for consideration only.
Attached Images
File Type: jpg EmployeesAndPositions_V0.jpg (66.2 KB, 246 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Crashing! Darrenc General 11 09-27-2012 01:52 AM
Database design assistance needed ddskorupski Theory and practice of database design 4 06-09-2009 01:17 AM
How do I change a password? Soapstreet General 1 05-02-2008 09:23 AM
Archiving A Database PC User Modules & VBA 2 01-07-2008 04:10 AM
Security in a Access97 Database Using Access2000 Smoothe932 General 0 11-28-2003 12:05 PM




All times are GMT -8. The time now is 02:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World