UPDATE Query, Created Temp Table Not working

[CoffeeFunnel]

Hi All,



I'm getting around to learning SQL, so I'm a bit new to advanced techniques. Can someone help me out a bit? What I'm trying to do, get the minimum value for a particular unique identifier from a transactional table, and update to another table that holds the records by unique identifier where the the minimum value is null. I have to crawl through multiple transactional records to complete the primary table. I keep getting a the message/error "Operation must use an updateable query." Below is the format of the query.

UPDATE PrimaryTable, (SELECT PrimaryTable.UniqueID, MIN(DataSourceTable.Value) AS MINValue FROM DataSourceTable INNER JOIN PrimaryTable ON DataSourceTable.UniqueID = PrimaryTable.UniqueID WHERE PrimaryTable.MINValue IS NULL GROUP BY PrimaryTable.UniqueID) AS TempTable SET PrimaryTable.MINValue = TempTable.MINValue WHERE (((PrimaryTable.UniqueID)=TempTable.UniqueID))

I tried UPDATE SET WHERE, but maybe my syntax was incorrect. I'm trying to avoid creating another table. I'd prefer to keep it all in one query. Is this possible?

Tanks!
CoffeeFunnel

[aytee111]

See here: http://www.fmsinc.com/Microsoftacces...ble/index.html

[CoffeeFunnel]

I read the article, made a few changes and I'm still getting the same message. I separated the sub query into it's own query, included a DISTINCT statement, and changed MIN to DMIN. But, I'm still getting the same error. The queries are below.

PrimaryTable

UPDATE PrimaryTable, TempQuery
SET PrimaryTable.MINValue = TempQuery.MINValue
WHERE (((PrimaryTable.UniqueID=TempQuery.UniqueID))

TempQuery

SELECT DISTINCT PrimaryTable.UniqueID, DMIN(SourceData.Value, "SourceData") AS MINValue
FROM SourceData INNER JOIN PrimaryTable ON SourceData.UniqueID=PrimaryData.UniqueID
WHERE PrimaryData.UniqueID IS NULL

I thought creating this temp table/query would resolve the issue, since it's seperating the actions and the DISTINCT/DMIN is not allowing repeats of UNIQUE IDs

[aytee111]

Table and query are two different things. Temp table/temp query are not the same. It has to be a table.

[CoffeeFunnel]

Okay, PrimaryTable Query works, because I made an actual table. I thought making a temp table was like making a subquery within the query. There has to be a way to combine all of this into one query.

Can someone point me in the right direction?