Update query based on another table

[apex77]

I am trying to create an update query that puts the word "house" in front of
sales person names in a table called "sales", that are not found in another
table called "salesPeople". I am familiar with update queries, but am not
sure how to modify the column in one table from the contents of another
table. For example, if the "salesPerson" table does not have "Sue Jones" then
"Sue Jones" in the "sales" would be updated to "House Sue Jones", somehow.
Make sense?

 

[KARL DEWEY]

You speak of two table but use three names ---
a table called "sales"
another table called "salesPeople".
if the "salesPerson" table

Post sample of your data before and after your desired update with table and
field names.

 

[apex77]

Sorry about that. There are only 2 tables, tblSalesPeople and tblSales. Here
are the 2 tables before the update query:

tblSalesPeople
ID SalesPerson
1 Joe Smith
2 Sue Turner
3 Rudy Jones

tblsSales
ID SalesPerson Amount
1 Joe Smith $450
2 Rudy Jones $250
3 James Smith $350

Here is the tblSales table after the update query that I cna;t seem to
figure out. Notice that where the name in the tblSales table was not found in
the tblSalesPerson table the sale is credited to "House":

tblSales
ID SalesPerson Amount
1 Joe Smith $450
2 Rudy Jones $250
3 House James Smith $350

Thanks for taking the time to look into this.

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You would seem to want to use a query like the following.

UPDATE tblSales LEFT JOIN tblSalesPeople
ON tblSales.SalesPerson = tblSalesPeople.SalesPerson
Set tblSales.SalesPerson = "House " & [tblSales].[SalesPerson]
WHERE tblSalesPeople.SalesPerson is Null
AND tblSales.SalesPerson NOT LIKE "House*"

I would not use an update query to do this, since you might add James Smith to
tblSalesPeople at some point in the future and then you might want to remove
House from all the tblSales records that involved him. I would probably
calculate the value when needed or at a minimum store "House" in a separate
field from the name.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[apex77]

Exactly what I needed. Thanks a ton!

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You would seem to want to use a query like the following.

UPDATE tblSales LEFT JOIN tblSalesPeople
ON tblSales.SalesPerson = tblSalesPeople.SalesPerson
Set tblSales.SalesPerson = "House " & [tblSales].[SalesPerson]
WHERE tblSalesPeople.SalesPerson is Null
AND tblSales.SalesPerson NOT LIKE "House*"

I would not use an update query to do this, since you might add James Smith to
tblSalesPeople at some point in the future and then you might want to remove
House from all the tblSales records that involved him. I would probably
calculate the value when needed or at a minimum store "House" in a separate
field from the name.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sorry about that. There are only 2 tables, tblSalesPeople and tblSales. Here
are the 2 tables before the update query:

tblSalesPeople
ID SalesPerson
1 Joe Smith
2 Sue Turner
3 Rudy Jones

tblsSales
ID SalesPerson Amount
1 Joe Smith $450
2 Rudy Jones $250
3 James Smith $350

Here is the tblSales table after the update query that I cna;t seem to
figure out. Notice that where the name in the tblSales table was not found in
the tblSalesPerson table the sale is credited to "House":

tblSales
ID SalesPerson Amount
1 Joe Smith $450
2 Rudy Jones $250
3 House James Smith $350

Thanks for taking the time to look into this.