Project Involving Table Relationships

[annemrosenberg]

So this may sound like a dumb question but I'm new to junction tables - does the data have to be all filled in or is it automatic?

[annemrosenberg]

Do I have to populate the junction tables with the data?

[TheShabz]

It has to be filled out manually. Thankfully, it's only at first and can be aided through automation in forms/VBA. Do a google search for "many to many relationship" and you'll see how a junction table is used as well as how to create forms to make data entry a bit easier.

[annemrosenberg]

So I think I've got all the tables set up correctly - so which are the PKs in each junction table? EmpSkill_ID and ProjSkill_ID?

[TheShabz]

Yep. The other two fields are foreign keys to their home tables.

[annemrosenberg]

Ok so everything is set up correctly. Now This is what My relationship looks like. Is this correct? Now I can use Design Query?

[TheShabz]

Yea that looks about right. You should be good to go?

[annemrosenberg]

Ok both the queries to match employees to skills and project to skills worked fine. Now how should I go about Matching the employee to the project?

[annemrosenberg]

Nevermind I think I got it! I used the two queries and joined them by Skill_ID to make a third query and it worked! Does that make sense?

[TheShabz]

Query the junction tables against eachother. One gives you all the skills for an employee. The other gives you all the skills required for a certain job.

Either way works. Query the queries or the junction tables directly.

[annemrosenberg]

Last Question - any ideas on to get it to work if only 2 out of 3 skills are a match?

[annemrosenberg]

Whats your address!? I need to send you a muffin basket or something for all this help. You saved me on this one!

[TheShabz]

You would run another query to get the count() of skills per job and the count() of skills per employee. Then do WHERE count(skills) - count(employee) = 1

That way, it's not just 2 out of three, but 1 less than whatever is required.

[TheShabz]

No need for muffin baskets but I will accept cash.

Mark the thread solved if that's all you need.

[annemrosenberg]

That's All I need!!!!!!!!! Thank you so much!!!!!!