How to run update query on 2 many-to-one tables

I have 2 many-to-one tables.

Both the Ppty_ID and UnitID fields are indexed and allow duplicate values in each table.

If the Ppty_ID and the UnitID fields are the same in both tables, I want one to overwrite the values in one of the tables based on a DateModified field.

I keep getting the error: "Operation must use an updateable query."

Neither has a primary key, and I can't use an autonumber field in each table to create a primary key.

Is there another workaround to get this done?

The query and tables are in the attached.

Thanks!
UpdateRecords.accdb
Patty01AccessAsked:
Who is Participating?
 
harfangCommented:
Based on some of your other questions, the combination of the fields Ppty_ID and UnitID is the primary key of both tables. Open the tables in design view, select both fields and click the “Primary Key” button. You query will then become updatable, as the Database Engine will know the the records can be identified unambiguously.

Cheers!
(°v°)
0
 
harfangCommented:
A point in terminology: there is no such thing as a “many-to-one” table. You can have “many-to-one” relationships between tables, but not tables as such. You could say for example: “I have two tables describing Units, both with a many-to-one relationship to Properties.”

(°v°)
0
 
als315Commented:
Sorry, Markus, do not working. I've never understood criteria of "non updatable query", except some obvious cases.
@Patty: I think you should add single PKs to your records in both tables. I've prepared an example for your deleted question and can upload it, but only After 20:00 (8 hours later)
UpdateRecords.accdb
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
harfangCommented:
als315,

That was interesting. I recreated the query and it worked. I then tried to edit the existing query to make it work also, but it somehow “remembers” the previous state of the tables and believes the query is not updatable. If you go to SQL view, and just copy-paste the entire query in place, it works. Normally, a compact and repair should take care of that sort of problem by removing the execution plan, but apparently not in this case.

Note that the query should use an INNER JOIN (you don't want to write into non-existing records at that point; that would be an INSERT query), and also that the field UnitID should not be updated (it is part of the join expression).

Cheers!
(°v°)
0
 
Patty01AccessAuthor Commented:
Color me stupid on this one!  

I always thought a primary key had to have "no duplicates." Harfang, you're totally correct.  I meant many-to-one relationships, not tables--working too long I guess.

And I encountered the exact problem you did, which was when I edit the query, it doesn't work, that it "remembers" the old stuff.  You have to either recreate it or edit-paste SQL.

Als, thanks for giving the table.  That always helps to see it.

Thank you, thank you both!!!
0
 
Patty01AccessAuthor Commented:
Ooops!!  I spoke too fast.  I tried it out on ALS sample, and it worked fine.

I can't figure out how you changed Ppty_ID to be a primary key and allow duplicate values.  Everytime I do it, I get the error: "Removing or changing the index of this field would require removal of the primary key" and it won't let me do it.

I'm using table design to change it.  I also tried creating a new table and have the same problem.  

What am I doing wrong?
0
 
Patty01AccessAuthor Commented:
It's obvious I need a vacation!!  Just realized you have to select BOTH the Ppty_ID AND UnitID, then click on Primary key for it to work with duplicate values.

I'm heading to Vegas to party!  You both have just made my day!!  Thanks again!
0
 
harfangCommented:
« I always thought a primary key had to have "no duplicates." »

And so it does, but it can be a unique combination of values in two or more fields.

Thanks for your feedback and have fun in Vegas!
(°v°)
0
 
als315Commented:
Markus, copy-paste in SQL view is great know-how. Never even tried to do it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.