By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Multiple table query not editing

P: 1
Hi all, I have been stuck on this issue for weeks now and can not find a solution, so I hoping that someone can help me out.

I am setting up a training database. I have setup the structure and started working on the forms and encountered an issue that has me baffeled.

***Table Info****
Employees have skill sets and it is a M:M relationship
Employees have training and it is a M:M relationship
Skill Sets have trainings and it is a M:M relationship

I have a form and I assign each skill set to employee(s). Some employees have multiple skill sets. Then I go into the type of Training (SOP) and assign the skill sets that need to be trained.

**PROBLEM***
Then I go into the employee form and select a certain employee from the list and go into that specfic employee.

The subform has a list of training(s) that employee has to complete. IE
Quality Control ---> The employee gets trained on QC and then completed is selected from the drop down menu. However, the Access DB does not allow it. The query seems as if though it is locked.

I have two intersection tables SkillSet_Details and SOP_Details in that query. When I remove the SOP_Details then my subform is not working.

I can't determine if this is my structure or my query. Please any help will be appreciated. I have attached a screenshot of the relationship
Table Relationship


Access 2003
Feb 13 '07 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Firstly, do your queries run OK?
Can you post the SQL of the queries that don't work and any relevant error messages reported when running them natively in a query.
Feb 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly you need to break down your many to many relationships by using join tables

Employees (Assuming primary key EmpID)
Training (Assuming primary key TrainingID)
SkillSets (Assuming primary key SkillSetID)

Remove any foreign keys to each other currently set up in these tables (After creating the following) and create join tables to hold them as follows:

EmpTraining (Primary Key {EmpID, TrainingID})
EmpSkills (Primary Key {EmpID, SkillSetID})
TrainingSkills (Primary Key {TrainingID, SkillSetID})

Now use these new tables in your queries to join the tables to each other.

All relationships should now be one to many.

Also have a look at this tutorial ...

Normalisation and Table structures

Mary
Feb 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.