Not Updateable Query (1 Viewer)

[FireStrike]

Hey all,

I am using Access 2010. I have two tables, tblInventoryItems, and tbl2R.
tblInventoryItems is just a list of items we have in out inventory. It has three fields, country_code(int), sim_mfr_no(text) and sim_item_no. The
tbl2R is a table with a number of replacements. tbl2R has the following fields.

newCC(int)
newMfr(text)
newItem(text)
newCat(text)
newDesc(text)
oldMfr(text)
oldItem(text)
oldCat(text)
oldDesc(text)

My primary keys in tbl2R is newCC, newMfr, newItem.
My Primary Keys in tblInventoryItems is country_code, sim_mfr_no, sim_item_no.

I created a query that will join these two tables. I need to update certain info on items that are in inventory. Unfortunately, access will not let me update the data through my query. Below is the SQL for my query.

SELECT tbl2R.newCC, tbl2R.newMfr, tbl2R.newItem, tbl2R.newCat, tbl2R.newDesc, tbl2R.oldMfr, tbl2R.oldItem, tbl2R.oldCat, tbl2R.oldDesc
FROM tbl2R INNER JOIN tblinventoryitems ON (tbl2R.newItem = tblinventoryitems.sim_item_no) AND (tbl2R.newMfr = tblinventoryitems.sim_mfr_no) AND (tbl2R.newCC = tblinventoryitems.country_code);

Thank you.

 

[spikepl]

I would suggest you try to do this in the query designer until it ticks. The SQL looks weird in my eyes.

 

[FireStrike]

I rebuilt the query in the query designer, and came up with the same problem. Can you think of anything else. Everywhere I looked says this thing should be updatable.

 

[spikepl]

Pass. Wait for someone more knowledgeable then MOI.

 

[Pat Hartman]

Are both tables local? Do both tables have primary keys defined?

 

[FireStrike]

Yes both tables are local and my primary keys are below.

My primary keys in tbl2R is newCC, newMfr, newItem.
My Primary Keys in tblInventoryItems is country_code, sim_mfr_no, sim_item_no.

 

[FireStrike]

Ok I figured this out. BTW I am using Access 2010. When you go to the design view you have to open up the properties. Once there you will see amung other two options, Unique Values, and Unique Records. You must set the Unique Records to Yes. Onmce this was done, it worked. When I looked at the SQL that it built, it added the DISTINCTROW directly after my select keyword. Anyone know why DISTINCTROW will work, but DISTINCT wil not?

 

[Pat Hartman]

The structure you posted is 1-1. Therefore joining the two tables on all three fields would not produce duplicate rows which is why no one suggested the unique values solution. I think your PKs are not what you think they are.

Here's a link to a description of the difference between Distinct and Distinctrow.
http://www.about-access-databases.com/sql-distinct.html