Help Creating a Training Record Database

10 pts.
Tags:
Access 2007
Microsoft Access
Microsoft Access database

We have an employee database that the managers want to use to track mandatory and option training. We already have an employee table, and I added two more.

tblEmployee   (EmpID,EmpName,EmpProgram,EmpStatus,HireDate)  Has the basic employee information

tblCourse       (CrsID,CrsName,CrsDescipt,CrsNum,CrsNeed,CrsFirstDue,CrsRenewFreq) Has the basic course information.

tblTraining      (trnID,EmpID,CrsID,TrnDueDate,TrnDoneDate) Connects the employee and course data. The due date will vary based on the onboarding date of the employee. The date completion date is needed to show when complete.

 The tables are combined in query to run reports.

qryTrainRecords          (EmpID,EmpName,EmpProgram,EmpStatus,CrsID,CrsName,CrsDescipt,CrsNum,CrsNeed,CrsFirstDue,CrsRenewFreq,TrnID,TrnDueDate,TrnDoneDate)

 

The problem with this approach is that the query will only show what training was entered in the tblTraining table.  In addition to showing that, I want to see what training was suppose to be assigned. I’ve tried different join methods in the query, but I’m not getting the results I wanted.  I want to be able to generate a report that shows all the employees and all the courses, and which employees has completed. Eventually, the dream would be to have ‘program profiles’. Any help in getting this started would be appreciated.



Software/Hardware used:
Access
ASKED: August 22, 2013  7:58 PM
UPDATED: August 23, 2013  3:04 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Since this is multiple tables hooked together in a relational query, and they also should be tied together with a common key (master key).  Some of the issues may be related to which table in the Query is the master table and which are the relational tables.  If you use the wrong table as the master table in the query it can give you odd results.  While it looks to me that your two questions related, to give you the proper results that you are looking for you may have to run two separate queries to get them.  To get the completed courses you may try using “is not null” in the completed field and to get the uncompleted courses “is null” in the completed field.  This may work for you.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following