min value in update query 1

[patrichek]

Hi,
in a previous post i prepared (with help)a query for an update query. Here is the original query code before the update:

SELECT Calls.ContactID, Min(Calls.CallDate) AS [date], Contacts.Source
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE Contacts.Source = 'website'
GROUP BY [Calls.ContactID], Contacts.Source;

When i click update query from the query builder menu, the group by min disappears and does not function when i run the update. How would i make this work using the min?

Here's my update query code:

UPDATE Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID SET Contacts.[Date received] = [calls].[calldate]
WHERE (((Contacts.Source)='website'));


thanks!

 

[PHV]

You may try this:
UPDATE Contacts
SET [Date received] = (SELECT Min(CallDate) FROM Calls WHERE ContactID = Contacts.ContactID)
WHERE Contacts.Source = 'website'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

PHV,
thanks for the response but i get "must use an updateable query" error?

 

[PHV]

And this ?
UPDATE Contacts
SET [Date received] = DMin('CallDate', 'Calls' ,'ContactID=' & Contacts.ContactID)
WHERE Contacts.Source = 'website'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

THANK YOU! it worked, PHV you are the best!!!!