Update query with data from another table

W

WayneM

I am trying to do an update of a column with data from another table. The
following T-SQL

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

doesn't work in Access. How can I do this in Access?

Thanks in advance,

WayneM
 
J

John Vinson

I am trying to do an update of a column with data from another table. The
following T-SQL

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

doesn't work in Access. How can I do this in Access?

Thanks in advance,

WayneM
Click to expand...

Use the DMax() *function* rather than a Totals subquery. Access is
picky: any totals query or any query including any total operations is
not updateable. Also, you need to use SQL-92 JOIN syntax rather than
the older SQL-88 "where clause join":

UPDATE Titles INNER JOIN Sales
ON Titles.title_id = Sales.title_id
SET ytd_sales = titles.ytd_sales + sales.qty
WHERE sales.ord_date = DMax("[Ord_date]", "[Sales]", "[title_id] = " &
[title_id]);

John W. Vinson[MVP]
 
Top