ACCESS 2010 SQL UPDATE SYNTAX

Question

looking for help w/ UPDATE syntax... thks in advance!

the following SELECT statement returns the record I'm looking for, but also needs to UPDATE the associated Claim.ICN field...

SELECT TOP 1 Claim.objName, First(Claim.objDate) AS FirstOfobjDate, Claim.ICN, Claim.CLM01, Claim.NM1IL
FROM Claim
GROUP BY Claim.objName, Claim.ICN, Claim.CLM01, Claim.NM1IL
HAVING (((Claim.CLM01)='ABC') AND ((Claim.NM1IL)='123'))
ORDER BY First(Claim.objDate) DESC;

something like the following?

UPDATE Claim SET Claim.ICN='123' IN (

SELECT TOP 1 Claim.objName, First(Claim.objDate) AS FirstOfobjDate, Claim.ICN, Claim.CLM01, Claim.NM1IL
FROM Claim
GROUP BY Claim.objName, Claim.ICN, Claim.CLM01, Claim.NM1IL
HAVING (((Claim.CLM01)='ABC') AND ((Claim.NM1IL)='123'))
ORDER BY First(Claim.objDate) DESC;

)

Answer 1

Hi Greg,

Make sure you have a backup. Try the following:

UPDATE Claim SET ICN="123" WHERE CLM01="ABC" AND NM1IL="123"

Hope it helps...

Answer 2

there are multiple records matching CLM01="ABC" AND NM1IL="123"...

I only want to update the most recent one (re: ORDER BY Claim.objDate DESC)...

suggestions?

Answer 3

Oh, I see. How about something like this?

UPDATE Claim SET ICN="123" WHERE CLM01="ABC" AND NM1IL="123" AND objDate=DMax("objDate","Claim","objName='" & [objName] & "'")

Hope it helps...

Answer 4

this most definitely helped! the DMAX function was the key!!!

thks again!!

Answer 5

this most definitely helped! the DMAX function was the key!!!

thks again!!

Hi Greg,

You're welcome. Glad we could assist. Good luck with your project.

Answer 6

Hi Greg Koby,

from your last post I find that your issue is solved now.

but you did mark the suggestion as an answer.

so this thread is still open.

please mark the suggestion given by .theDBguy as an answer.

so that we can close this thread.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].