update query

  • Thread starter Thread starter Rohan via AccessMonster.com
  • Start date Start date
R

Rohan via AccessMonster.com

Help!

I have two tables:

Compliance
compliance_id autonumber (primary key)
trigger_date date/time

compliance_level
compliance_level_id autonumber (primary key)
compliance_id number (foriegn key)
notification_date datetime

And I want to set Compliance.trigger_date to latest compliance_level.
notification_date

How do I do it ?

Rohan
 
You will have to use one of the vba domain functions DMAX to do this in one
query. Access will not let you use any of the SQL aggregate operators in an
update query.

UPDATE Compliance
SET Trigger_Date =
DMAX("Notification_Date","Compliance_Level","Compliance_id =" & Compliance.ComplianceID)

Other way is to build a table with just two fields and append records to it -
the compliance_level.Compliance_ID and the Max of the notification date. Then
use that built temp table to update the compliance table. This may be the way
to go if you have a large number of records to update (many thousands).

Query to Append records to existing temporary table:
INSERT INTO TempTable (Compliance_ID, Notification_Date)
SELECT COmpliance_ID, Max(Notification_Date) as LastDate
FROM Compliance_Level
GROUP BY Compliance_ID

Query to Update records with the results of above:
UPDATE Compliance INNER JOIN TempTable
 
Cheers John.

I tried to implement the second suggestion using a query instead of a temp
table,
and sourcing the query in the second update query, but it gave me errors
telling me
it wasn't an update query.

Will implement your solution.

Thanks again,

Rohan.
 
Yes, that was my point, you can't use any SQL aggregate functions in an
update query. Since the source query had an aggregate sql function in it,
then it can't be used to update the data in the target query. You can use
the SQL aggregate functions in append queries.

The only place you can use aggregate SQL functions in an update query is in
the where clause. Unrealistic example follows that allows you to use one of
the aggregate functions in an Update query.

UPDATE TableX
SET TableX.TheValue = 22
WHERE TableX.TheValue = (SELECT MAX(T.TheValue) FROM TableX as T)
 
Back
Top