Solved Query Not Updateable (1 Viewer)

[Weekleyba]

How do I explain why this query is not updatable?

 

[Pat Hartman]

You have a parallel relationship. Two tables are related by projectID but they are not dependent on each other. This will likely end up as a Cartesian Product and that will be the problem.

Think of this silly situation. You have Student, Class, Pet. Class and Pet are related to student but not to each other. So if you create a query that joins these three tables, you end up with:
Joe, Math, Tiger
Joe, English, Tiger
Joe, Science, Tiger
Joe, Math, Spot
Joe, English, Spot
Joe, Science, Spot
Sally, Math, Patches
Sally, Science, Patches
Sally, Math, Poo
Sally, Science, Poo
Sally, Math, Buster
Sally, Science, Buster

 

[The_Doc_Man]

That isn't a query, it's a relationship diagram. Show us the SQL of your query. You can open the query in design view then switch views to SQL and do a copy/paste.

EDIT: Pat is looking at it correctly in absence of an exact SQL statement. I'll put the problem in words. A query can be updated if its criteria uniquely identify all of the factors that could apply equally to multiple records. If you have multiple projects then the ProjectID can be ambiguous in TJ_ProjectLocation and T_PA unless you constrain that ID.

I'll add one more observation. Your PA_Amount1...PA_Amount8 tell us that your tables are not properly normalized, which will cause trouble down the road. Please look up "Database Normalization" and consider that your T_PA table is not correctly designed.

 

[Pat Hartman]

You might be able to resolve the issue by nesting the queries. Create query1 that joins the two right side tables. Then Create query2 that joins query1 to projectlocation and Location. By coercing the join order, you might solve the problem and Access will let you update. Otherwise use the first three tables for the main frm and the fourth table as a subform which is probably what you need anyway.

 

[Weekleyba]

You have a parallel relationship. Two tables are related by projectID but they are not dependent on each other. This will likely end up as a Cartesian Product and that will be the problem.

Think of this silly situation. You have Student, Class, Pet. Class and Pet are related to student but not to each other. So if you create a query that joins these three tables, you end up with:
Joe, Math, Tiger
Joe, English, Tiger
Joe, Science, Tiger
Joe, Math, Spot
Joe, English, Spot
Joe, Science, Spot
Sally, Math, Patches
Sally, Science, Patches
Sally, Math, Poo
Sally, Science, Poo
Sally, Math, Buster
Sally, Science, Buster

Thanks Pat the parallel relationship explains it.

 

[Weekleyba]

That isn't a query, it's a relationship diagram. Show us the SQL of your query. You can open the query in design view then switch views to SQL and do a copy/paste.

EDIT: Pat is looking at it correctly in absence of an exact SQL statement. I'll put the problem in words. A query can be updated if its criteria uniquely identify all of the factors that could apply equally to multiple records. If you have multiple projects then the ProjectID can be ambiguous in TJ_ProjectLocation and T_PA unless you constrain that ID.

I'll add one more observation. Your PA_Amount1...PA_Amount8 tell us that your tables are not properly normalized, which will cause trouble down the road. Please look up "Database Normalization" and consider that your T_PA table is not correctly designed.

Thanks Doc Man. You are correct, I need to normalize this table. This brings me to another problem that I'll post separately.