Update Query with multiple tables

[racquetballer]

I need to to an update query that involves three tables: table Dealer
needs to be updated with data from table Personnel and table Title.

Dealer is joined to Personnel where Dealer.Dealer_Code =
Personnel.so_cd
Personnel is linked to Title by Personnel.name_id = Title.name_id

I need to update fields Principal_First_Name, Principal_Last_Name, and
Company_Email in Dealer from FirstName, LastName, and
InternetEmailAddr in Personnel where Title in PersonnelTitle = 'DM" or
"Dealer Principal".

I'm trying to do an update query in Access, and here is the SQL that
is generated:

UPDATE dbo_TABLE_Dealer INNER JOIN (Personnel INNER JOIN
PersonnelTitle ON Personnel.name_id = PersonnelTitle.name_id) ON
Dealer.Dealer_Code = Personnel.so_cd SET Dealer.Principal_Last_Name =
Personnel.LastName, Dealer.Principal_First_Name = Personnel.FirstName,
Dealer.Company_Email = Personnel.InternetEmailAddr
WHERE (((PersonnelTitle.Title) In ("DP","Dealer Principal")));

However, when I try to run it, I get a message that says "Operation
must use an updateable query". How do I need to change the query to
do what I need to do?

Thanks.

Steve

[Allen Browne]

Steve, the first thing to check out would be if there is a unique index on
the fields used in the join.

You joined Personnel.name_id to PersonnelTitle.name_id.
One of those tables will be the "one" side of the join.
Open that table in design view, and either mark the field as primary key, or
set the Indexed property to "Yes (No Duplicates)" (in the lower pane of
table design.)

Similarly, you joined Dealer.Dealer_Code to Personnel.so_cd.
Again, the field on the "one" side needs a unique index.

There's a list of things here:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
but the indexing is the most likely possibility.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ra***********@hotmail.comwrote in message
news:11**********************@b75g2000hsg.googlegr oups.com...

>I need to to an update query that involves three tables: table Dealer
needs to be updated with data from table Personnel and table Title.

Dealer is joined to Personnel where Dealer.Dealer_Code =
Personnel.so_cd
Personnel is linked to Title by Personnel.name_id = Title.name_id

I need to update fields Principal_First_Name, Principal_Last_Name, and
Company_Email in Dealer from FirstName, LastName, and
InternetEmailAddr in Personnel where Title in PersonnelTitle = 'DM" or
"Dealer Principal".

I'm trying to do an update query in Access, and here is the SQL that
is generated:

UPDATE dbo_TABLE_Dealer INNER JOIN (Personnel INNER JOIN
PersonnelTitle ON Personnel.name_id = PersonnelTitle.name_id) ON
Dealer.Dealer_Code = Personnel.so_cd SET Dealer.Principal_Last_Name =
Personnel.LastName, Dealer.Principal_First_Name = Personnel.FirstName,
Dealer.Company_Email = Personnel.InternetEmailAddr
WHERE (((PersonnelTitle.Title) In ("DP","Dealer Principal")));

However, when I try to run it, I get a message that says "Operation
must use an updateable query". How do I need to change the query to
do what I need to do?

Thanks.

Steve