Updating one tables value based on another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BSIMONS - 08 Nov 2005 02:33 GMT

Hello,

I have two tables, table1 and table2.
Table1 contains fields named "BTN", "product", "sale_date", "product_type"
There are various products in the product table with a date for each sale.
The sales field contains 1 of 4 values that desccribe the catagory of each
product sale (i.e, Line_Prod, Data_Prod, LD_Prod, Feat_Prod)

Table2 contains fields "Line_Prod_Sale", "Line_Prod_Sale_Date",
"Data_Prod_sale", "Data_Prod_Sale_date"

Both tables are joined using the "BTN"

What I want to accomplish is:
step 1
If BTN matches between each table, table1.product_type=Line_prod, update
table2.line_prod_sale from value in table1.product and update
table2.line_prod_sale_date from table1.sale_date
step 2
table1.product_type=data_prod, update table2.data_prod_sale from value in
table1.product and update table2.Data_prod_sale_date from table1.sale_date

I hope this makes sense to someone besides me!

Thanks

Reply to this Message

Michel Walsh - 08 Nov 2005 12:21 GMT

Hi,

==================================
step 1
If BTN matches between each table,
table1.product_type=Line_prod,
update
table2.line_prod_sale from value in table1.product
and update
table2.line_prod_sale_date from table1.sale_date

step 2
table1.product_type=data_prod,
update
table2.data_prod_sale from value in table1.product
and update
table2.Data_prod_sale_date from table1.sale_date

====================================

The updated instructions seem the same in both steps? So I assume that if
one condition OR the other is observed, then the update has to be performed,
I am right? If so:

UPDATE table1 INNER JOIN table2
ON table1.BTN=table2.BTN
AND ( table1.product_type=Line_prod
OR
table1.product_type=data_prod)
SET
table2.line_prod_sale = table1.product,
table2.Data_prod_sale_date= table1.sale_date

Hoping it may help,
Vanderghast, Access MVP

> Hello,
>
[quoted text clipped - 21 lines]
>
> Thanks

Reply to this Message