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

·
Registered
Joined
·
54 Posts
Discussion Starter · #1 ·
Hello everyone. Thanks again for all of your help in the past. I have come to another road block and was wondering if anyone could help me with this problem.
I have an Employee table, an inservice(training sessions) table, and a junction table to join them. Works fine to input data and find out who took a particular inservice. The problem is when we need to find out who didn't take a particular inservice. Here is the database with sample data. It seems like an easy answer but I am not getting it. Thanks in advance!
Leza
 

Attachments

·
Registered
Joined
·
19,896 Posts
Leza, I am looking at your database now, do you want to check any particular inservice, or do you want the ability to be able choose one?
 

·
Registered
Joined
·
19,896 Posts
Leza, the attached database has a form called missing inservice.
That has a Combo that will allow you to select an inservice to search for that employees have not taken.
 

Attachments

·
Registered
Joined
·
54 Posts
Discussion Starter · #6 ·
Thanks for taking the time to create the form OBP!
I may have changed the data a bit so here is another copy. When I search for who did not take the inservice 1 the answer should be Susan and New Person. When I search for who did not take inservice 2, I should get Mary, Bob, and New person. When I search for who didn't take inservice 3 the answer should be Bob, Susan and New Person. People are going to be taking several different inservices and this person has to track down who hasn't taken a particular inservice yet.
 

Attachments

·
Registered
Joined
·
19,896 Posts
Leza, I see what you mean, because Mary Jones has taken service 1, and service 3 she shows up when she shouldn't.
This will take a bit of extra thought.
 

·
Registered
Joined
·
4,193 Posts
A couple of things for you. Your Active field should be a Yes/No data type and should be checked off when they are actually active. If you want an explanation as to how he created the query and got the results as a learning tool then feel free to let us know. I do teach as well and have a training database that I customized from a template. So again let me know if you want to explore other things and get other suggestions.
 

·
Registered
Joined
·
19,896 Posts
Jim, can you see any other way to get it to work, it is not very elegant?
The problem is the that you have to account for users not in the link table as well as those that are in there more than once.
I could of course do it with Recordsets and VBA.
 

·
Registered
Joined
·
4,193 Posts
You did it the right way by changing the join types. At least that is how I would have done it. You can also set it up to look for people who have taken no classes as well. I use that in one of my lessons. ;)
 

·
Registered
Joined
·
54 Posts
Discussion Starter · #13 ·
OBP did a great job! He made this work perfectly. but...should I have designed the tables differently? There are many people taking many classes so the only thing I could come up with was a junction table. Is there a better way? This is still in the early stages, so I could probably do a redesign if I needed to - to alleviate future problems. Thanks! Leza
 

·
Registered
Joined
·
4,193 Posts
No a Many-To-Many Relationship was the right thing to do here. Did you change the suggestion that I made? I did not look into the structure that much, I just noticed that in passing.
 

·
Registered
Joined
·
54 Posts
Discussion Starter · #15 ·
THanks Jimr381, Actually, I didn't want to take too much of your time, but I didn't really know what you meant by active field. Thanks for your help!! Leza
 

·
Registered
Joined
·
4,193 Posts
You have an field within your employees table which says if they are an active employee or not. This should changed to a yes/no data type.
 

·
Registered
Joined
·
100 Posts
I would like to learn can I get that trainging database and ask questions on that?? to learn on how to do this... I can make tables, fine and reports desgin fine. but expressions and queries are where I go crazy!! They get very complicated and it seems if I understand why you must do this or that it helps to understand it better... I am all for learning this wonderful complex program... Then maybe I could give back and help others as you all do so very much!!
 

·
Registered
Joined
·
4,193 Posts
It will take a little bit before I can clean out my data, images and such and upload it. I am thinking probably towards the middle of the week since I am knee deep in doing my course ware for next weeks class.
 

·
Registered
Joined
·
54 Posts
Discussion Starter · #20 ·
Yes Jim, now I know what you are referring to. We made that a text field because it could me more than just a yes or no. for example, Maybe active but temp employee. I know the database I zipped doesn't indicate that though.
 
1 - 20 of 21 Posts
Status
Not open for further replies.
Top