UPDATE TOP value

[john.bartlett]

I am having some issues with selecting the TOP value in an UPDATE
query. I am not an expert and I am looking for advice on the best way
to proceed.

Here is a UPDATE query I am using that works. It updates the
PrizeAssigned field to "1" in the Main Table where Weight1 is equal to
200.

dbs.Execute "UPDATE Main " & _
"SET PrizeAssigned = 1 " & _
"WHERE tblMain.[Weight1] = 200;"

The problem I have is that I don't want to have to specify the value
for Weight1. I want Access to select the Weight1 record with the
greatest value. This is how I think it should be but I can't figure
out the syntax to replace "SELECT TOP 1" in this statement?

dbs.Execute "UPDATE tblMain " & _
"SET PrizeAssigned = 1 " & _
"WHERE tblMain.[Weight1] = SELECT TOP 1;"


Any advice/guidance is appricated.
Thanks,
JB

 

[John Spencer]

Perhaps the following:

UPDATE tblMAIN
SET PrizeAssigned = 1
WHERE tblMain.Weight =
(SELECT Max(Weight)
FROM tblMain)

or

UPDATE tblMAIN
SET PrizeAssigned = 1
WHERE tblMain.Weight IN
(SELECT TOP 1 Weight
FROM tblMain
ORDER BY Weight DESC)


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[john.bartlett]

That works! Thank you for your help.

 

[john.bartlett]

So, this works great (thank you John Spencer), but I need to add one
more condition. Is there a way to only consider updating records in
the MAIN table where MAIN.PrizeAssigned = 0 ?

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
FROM tblMain)));

 

[John Spencer]

Add another condition to the where clause

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
FROM tblMain)))

AND tblMain.PrizeAssigned = 0

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

So, this works great (thank you John Spencer), but I need to add one
more condition. Is there a way to only consider updating records in
the MAIN table where MAIN.PrizeAssigned = 0 ?

Sure. Just add that as another criterion.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
FROM tblMain)))
AND tblMain.PrizeAssigned = 0;

 

[john.bartlett]

Thanks again for taking the time to address my questions. Mahalo.