• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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
0
Patty01Access
Asked:
Patty01Access
  • 4
  • 3
  • 2
3 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now