Update from two tables

[Guest]

Hi, I want to set a field equal to a field in another table where two fields
match (if that makes sense.

Project Number is Text, Month is Integer, Cumulative Total and Total Order
Value are Currency

SQL:
UPDATE [Tbl_View Detailed] SET [Tbl_View Detailed].[Total Order Cover] =
[Tbl_View Variations With Cumulative].[Cumulative Total]
WHERE ((([Tbl_View Detailed].[Project Number])=[Tbl_View Variations With
Cumulative].[Project Number]) AND (([Tbl_View Detailed].Month)=[Tbl_View
Variations With Cumulative].[Month]));

Keeps asking me for the values from [Tbl_View Variations With Cumulative]
when they are in the table.

 

[Carl Rapson]

Hi, I want to set a field equal to a field in another table where two
fields
match (if that makes sense.

Project Number is Text, Month is Integer, Cumulative Total and Total Order
Value are Currency

SQL:
UPDATE [Tbl_View Detailed] SET [Tbl_View Detailed].[Total Order Cover] =
[Tbl_View Variations With Cumulative].[Cumulative Total]
WHERE ((([Tbl_View Detailed].[Project Number])=[Tbl_View Variations With
Cumulative].[Project Number]) AND (([Tbl_View Detailed].Month)=[Tbl_View
Variations With Cumulative].[Month]));

Keeps asking me for the values from [Tbl_View Variations With Cumulative]
when they are in the table.

It's asking for that because it doesn't know what [Tbl_View Variations With
Cumulative] is. Try something like this:

UPDATE [Tbl_View Detailed] INNER JOIN [Tbl_View Variations With Cumulative]
ON ((([Tbl_View Detailed].[Project Number])=[Tbl_View Variations With
Cumulative].[Project Number]) AND (([Tbl_View Detailed].Month)=[Tbl_View
Variations With Cumulative].[Month]))
SET [Tbl_View Detailed].[Total Order Cover] = [Tbl_View Variations With
Cumulative].[Cumulative Total];

Disclaimer: I don't know if this will work as is, but I've seen syntax like
this before in these newsgroups.

Carl Rapson