manyto many, prob a basic q

[Muzicmn]

I have what appears to be a simple problem but for the life of me i cannot find a solution, DB has not been developed yet so i cannot post a sample

situation: have a group of employees, each month these employees need to complete some sort of training, some months only one others as many as 7. anyway, i have been tasked to develop a db to track this.

sounds simple but i cannot seem to figure out how to do it. i need to have a form where the employer can enter a new training item and have it relate to all the employees. so when he runs a query it shows which employee has not completed this training item.

so i am stumped with how do i assign a new training item to all 30+ employees and have it place some sort of checkbox to show completion

any ideas would be greatly appreciated

Ricky

 

[RichO]

This can be accomplished in a one-to-many relationship. For instance, you have your one-side table (tblEmployees) containing all of the employee information, the Social Security # makes a perfect primary key because there can be no duplicates.

In your many-side table (tblTraining) you have 3 fields. Employee ID (SS#), training item, and a Yes/No field showing if the course is completed. You would then run an append query whenever you want to add a new training item to tblTraining for each employee.

With this simple table structure you can query the information by employee, course, completed status, etc...

 

[Pat Hartman]

You really have a many-to-many relationship. Rather than creating a new record for each employee when a new class is required, just add the record to the training table. Then as each employee completes the training, add a record to the employeetraining table with the completion date.

tblEmployee
EmployeeID (autonumber pk)
LastName
FirstName
SSN
etc.

tblTraining
TrainingID (autonumber pk)
SubjectName
DueDate
etc.

tblEmployeeTraining
EmployeeID (pk fld1, foreign key to tblEmployee)
TrainingID (pk fld2, foreign key to tblTraining)
CompletionDate

To identify who has not yet been trained, use an unmatched query between tblEmployee and tblEmployeeTraining for a particular TrainingID. The records in tblEmployeeTraining indicate who has been trained.

My method has a slight advantage because new employees will also be required to take the training even if they are added to the system after the training record is added.

Download my sample many-to-many db to see how this structure works. The situation is a little different if only certain people are required to take a particular class. You would then add the rows to tblEmployeeTraining to indicate that the class was required and the date would be filled in when it was completed.

 

[Muzicmn]

thanks for the quick response, will give it a try

Ricky

 

[Muzicmn]

found your sample db and just downloaded it, now to figure it out.....

i kinda figured it was a many to many, but wasnt sure....

i appreciate the first response about the append query as well, didnt even know that existed am sure i will have the need for that one as well

thanks alot guys for all your advice....

Pat, is that Patrick or Patricia???

thanks a million

Ricky

 

[Pat Hartman]

My name is Patricia.

Good luck.

 

[Muzicmn]

Well Patricia,

thank you so much, i tried your advice and it makes sense but i am having a pblm with the query. if you, or anyone else here dont mind taking a look at it, i created a very simple db based on what you said but i cannot get the query to display those that have not taken a particular course

i am sure its an operator error on my part,

thanks again

Ricky

 

[Pat Hartman]

Sorry, my post wasn't complete. When your criteria is applied to the many-side table, it must be performed in a separate query, before the final join. I separated the query into two queries. One obtains people with particular training and prompts for a TrainingID, the second joins the employee table to that query with a left join and selects only employees that are NOT in the first query.

I changed all your names and I added the relationship to the relationship window and enforced RI.

You should get into the habit of properly naming objects from the beginning. That way you won't need to do it later when it is a larger task.

PS, how do you get that EULA screen to pop up even when the db is opened with the shift key? I didn't spend much time looking but couldn't find any trace of it. Looks like a nice trick you might share.

 

[Muzicmn]

lol, i was afraid you were gonna ask that, i am using access 2003 and it comes on all the time, was gonna try and find out how to get rid of it....

anyway, anyone willing to look into it and see if they can figure it out, Pat and I will appreciate it, in the meantime i will be looking myself

Pat, works great, you know its funny....I was beating my head against the wall trying to figure out how to mark that they did not complete the training, when all i had to do was think differently and mark who did and then filter it from there..... i guess i need to step back sometimes and look at it from another angle.

thanks again for your time and expertize,

Ricky

 

[Muzicmn]

Pat,

or anyone that can help here, the db you made works great but i am stumped on one thing is there anyway now that we are using 2 queries to accomplish this, that i can query all emps that have not taken training. I guess what i am asking for is instead of entering a particular class to search by can i create a report using all training being offered. kinda like a master list to see who needs the training without having to query each individual course. I have tried ammending the queries we have by using the "Like "*"" or by "is not null" and a number of other off the wall ideas but the second query wont show any records

thanks alot

Ricky

 

[Pat Hartman]

why doesn't this work for you?

SELECT tblEmployee.EmployeeID, tblEmployee.Last, tblEmpTraining.TrainingId
FROM tblEmployee LEFT JOIN tblEmpTraining ON tblEmployee.EmployeeID = tblEmpTraining.EmployeeID
WHERE (((tblEmpTraining.TrainingId) Is Null));


PS, NEVER use Like unless you are searching for partial strings. Also, like only works properly on string data types. It is not intended for use with numeric data types.

 

[rramon]

Unmatched Question

The situation is a little different if only certain people are required to take a particular class. You would then add the rows to tblEmployeeTraining to indicate that the class was required and the date would be filled in when it was completed.

I have the following tables:

Employees:
EmployeeID
FirstName
LastName
JobTitle

Courses:
CourseID
Course

CoursesRequired:
JobTitle
Course

CompletedTraining:
EmployeeID
CourseID
CompletionDate

I can make the unmatched query work using only the Employee Table and the Completed Training Table but I can't figure out how to include the CoursesRequired Table. I'm thinking I need two seperate queries and then run and unmatched query on the 2 new queries. Any help would be appreciated. Thank you.

Rocky