Update Query Error

[Pete]

I am using Access 2007.

I have 2 tables tblStock and tblStockPriceHistory. Each night I append the
latest stock price data to the tblStockPriceHistory. I then want to run an
update query to update tblStock to show the date of the most up to date data.

My first query (qryStockPriceHistoryLastDate ) finds the maximum date for
each stock from tblStockPriceHistory.

SELECT DISTINCT tblStockPriceHistory.cStockCode,
Max(tblStockPriceHistory.dClosingDate) AS MaxOfdClosingDate
FROM tblStockPriceHistory
GROUP BY tblStockPriceHistory.cStockCode;

I then wish to update the corresponding stock record in tblStock. When I try
to run the following query I get this error "Operation must use an updateable
query"

UPDATE tblStock INNER JOIN qryStockPriceHistoryLastDate ON
tblStock.cStockCode = qryStockPriceHistoryLastDate.cStockCode SET
tblStock.dLastUpdated = [qryStockPriceHistoryLastDate].[MaxOfdClosingDate];

 

[Pete]

OK answered my own question. The problem is due to Access' poor support of
the ANSI 92 of SQL with regard to UPDATE queries. I had hoped this might have
been addressed in this version... there's always 14 ;-) ...

The solution is to use the DMax function, but it is slow.

UPDATE tblStock INNER JOIN tblStockPriceHistory ON tblStock.cStockCode =
tblStockPriceHistory.cStockCode SET tblStock.dLastUpdated =
DMax("dClosingDate","tblStockPriceHistory","cStockCode ='" &
[tblStock].[cStockCode] & "'");

 

[John Spencer]

I don't know but using a correlated subquery M I G H T help speed this up if
you really only need to update the changes and there aren't a lot of them.

UPDATE tblStock INNER JOIN tblStockPriceHistory
ON tblStock.cStockCode = tblStockPriceHistory.cStockCode
SET tblStock.dLastUpdated =
DMax("dClosingDate","tblStockPriceHistory","cStockCode ='" &
[tblStock].[cStockCode] & "'")
WHERE tblStock.dLastUpdated is Null
Or tblStock.dLastUpdated <>
(SELECT Max(dClosingDate)
FROM tblStockPriceHistory as H
WHERE H.cStockCode = tblStock.cStockCode)

As I say it may or may not be faster.


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

OK answered my own question. The problem is due to Access' poor support of
the ANSI 92 of SQL with regard to UPDATE queries. I had hoped this might
have
been addressed in this version... there's always 14 ;-) ...

The solution is to use the DMax function, but it is slow.

UPDATE tblStock INNER JOIN tblStockPriceHistory ON tblStock.cStockCode =
tblStockPriceHistory.cStockCode SET tblStock.dLastUpdated =
DMax("dClosingDate","tblStockPriceHistory","cStockCode ='" &
[tblStock].[cStockCode] & "'");


--
Peter Schmidt
Ross-on-Wye, UK

I am using Access 2007.

I have 2 tables tblStock and tblStockPriceHistory. Each night I append
the
latest stock price data to the tblStockPriceHistory. I then want to run
an
update query to update tblStock to show the date of the most up to date
data.

My first query (qryStockPriceHistoryLastDate ) finds the maximum date for
each stock from tblStockPriceHistory.

SELECT DISTINCT tblStockPriceHistory.cStockCode,
Max(tblStockPriceHistory.dClosingDate) AS MaxOfdClosingDate
FROM tblStockPriceHistory
GROUP BY tblStockPriceHistory.cStockCode;

I then wish to update the corresponding stock record in tblStock. When I
try
to run the following query I get this error "Operation must use an
updateable
query"

UPDATE tblStock INNER JOIN qryStockPriceHistoryLastDate ON
tblStock.cStockCode = qryStockPriceHistoryLastDate.cStockCode SET
tblStock.dLastUpdated =
[qryStockPriceHistoryLastDate].[MaxOfdClosingDate];