Update Query - Access 2000

[ste4en]

I have the following table and I need to update the IsObsolete field.

Item, ReceivedDate, IsObsolete
1, 6/5/01,
1, 6/6/05,
2, 1/1/05,
2, 2/2/05,
2, 2/15/05,
2, 3/2/05,
2, 6/6/06,
3, 2/2/05,

I need to evaluate every item and make the item with the latest receiced date FALSE i.e. not obsolete; and make all the others true, example below.

Item, ReceivedDate, IsObsolete
1, 6/5/01, TRUE
1, 6/6/05, FALSE
2, 1/1/05, TRUE
2, 2/2/05, TRUE
2, 2/15/05, TRUE
2, 3/2/05, TRUE
2, 6/6/06, FALSE
3, 2/2/05, FALSE

How can I do this. I have about 15,000 records so need some automation.

Thanks

Thanks

 

[PHV]

A starting point:
UPDATE yourTable
SET IsObsolete = True
WHERE ReceivedDate < DMax("ReceivedDate", "yourTable", "Item=" & [Item])

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

 

[ste4en]

Thanks for the response, I tried what you suggest but I am doing something wrong, can you spot it, I can't upload the sample database but I made a picture of it at the link below, thanks

 

[PHV]

[tt]< DMax("recDate", "Table1", "number='" & [number] & "'")[/tt]

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

 

[ste4en]

That did it - Thank You