Update Value In A Table From Another Table

[lhooker]

I'm trying to update a value in a table from another table. I'm getting zero records in return. Can anyone see why my query does not work. The tables and "Update" query are below.


Table1 --- Column to be replaced

Payee --- Category
John --- Manager --- Fulltime
Jane --- Clerk --- Fulltime
James --- Account --- Fulltime
Joan --- Assistant Manager --- Fulltime
Jenny --- Technical Advisor --- Fulltime


Table2 --- Column replacement

Payee --- Category
John --- Account --- Deli Department
Jane --- Assistant Manager --- Shoe Department
James --- Manager --- Produce Department
Joan --- Clerk --- Cashier
Jenny --- Technical Advisor --- IT Department


Table1 --- Final results

Payee --- Category
John --- Account --- Fulltime
Jane --- Assistant Manager --- Fulltime
James --- Manager --- Fulltime
Joan --- Clerk --- Fulltime
Jenny --- Technical Advisor --- Fulltime

Query is below

UPDATE Table1 INNER JOIN Table2
ON Table1.Account = Table2.Category
SET Table1.Account = [Table2].[Category]
WHERE (((Table2.PayeeSelectBox)=[Payee]));

 

[plog]

You're joining the two tables on the field you want to update. The query you've built can update Table1, but its only going to update it to a value it already has:

ON Table1.Account = Table2.Category 
SET Table1.Account = [Table2].[Category]

So if Table1.Account = 4 and Table2.Category=4 you would update Table1.Account to 4. If Table1.Account doesn't equal Table2.Category, then the query skips over that record. You need to join and update on different sets of fields.

 

[lhooker]

Plog,

I added an additional column to both tables (i.e. "Accountx" and "Categoryx") and changed the "Update" query. I'm getting the same results. Did I understand your recommendations correctly ? Below is the current code. Thanks for your help ! ! !


UPDATE Table1 INNER JOIN Table2
ON Table1.Account = Table1.Category
SET Table1.Account = [Table2].[Categoryx]
WHERE (((Table2.PayeeSelectBox)=[Payee]));

 

[plog]

INNER JOIN Table2 
ON Table1.Account = Table1.Category

That's not a valid join. One of those fields needs to be from Table2.

At this point you might need to post data. Give me 3 sets:

A. Starting data in Table1
B. Starting data in Table2
C. Expected data in Table1 after update.

Be sure to include field names and enough examples to cover all cases.

 

[lhooker]

Plog,

Here ya go ("Test.mdb") . . . I'm trying to update the blank field ("Payee") in Table1 with the value in Table2 ("PayeeSelectBox"). The "Account" in Table1 must match "Category" in Table2. Thanks again for your help ! ! !

 

[plog]

Ok, you gave me A & B. Now provide me with C.

C. Expected data in Table1 after update.

 

[lhooker]

Plog,

I tried to first put what I wanted in words. I created a "Test" database hoping to clarify what I needed. Is it still unclear ?

 

[plog]

Yes. No words, data.

 

[lhooker]

Plog,

Are the tables ("Table1" and "Table2") in the database ("Test") ? I created a "Test" database and query (i.e. "QueryUpdate_Payee_In_Table1") to make it easy for you. If you can assist with the "Test" database, that will be fine. Hopefully, this is not a problem. Thanks ! !

 

[plog]

Yes they are and no you are not making things easy. 5 posts now we've done this dance. If you would like me to help you please have your next post contain C: expected data.

 

[lhooker]

Plog,

There is no "C" table. The final result will be an updated "Table1" with the "Payee" in the blank fields.

 

[lhooker]

Plog,

Never mind, you don't have to respond. It seems you are frustrated with my responses. I'll seek a resolution elsewhere. Thanks for your responses ! ! !

 

[Frothingslosh]

lhooker - you've given him the starting data. He wants you to provide the data you expect to be output - the speific data you expect to see in the end. Not a generality, but the actual finished output you're expecing.

We know you want to update table 1 based on table 2. You have provided table 1 and table 2. You have NOT provided what you want table 1 to look like after the update, and your SQL statement is, unfortunately, of no help, because it looks for records where account = category, and in those records sets account to equal category, which it already does.

If you post the data you're EXPECTING to get as a result, we can help you. Otherwise, we're just going to go in circles here.