Update where number is largest?

[pdbowling]

Hey all,
I have a table like this

Unit.........unitCount........WoNum
F001....... 1........xxxx
F001....... 2........
F002....... 1........xxxxx
F002....... 2........
F002....... 3........xxxxx
F002....... 4........
F003....... 1........xxxxx
F004....... 1........xxxxx
etc

I need to write an update query something like.....

"Update table set WoNum = myVariable where
Unit = myUnitVariable AND
unitCount is the largest" <-- this being my confusion.

WoNum is not required so may be empty so ...
where woNum = "" will not always work.

Any suggestions out there.
Thanks everyone.
PB

 

[Jack Cowley]

Try this:

UPDATE MyTable SET MyTable.WoNum = varMyVariable
WHERE MyTable.Unit=MyUnitVariable AND MyTable.UnitCount=DMax("[WoNum]","MyTable");

hth,
Jack

 

[namliam]

You have:
Unit.........unitCount........WoNum
F001....... 1........A
F001....... 2........
F002....... 1........B
F002....... 2........
F002....... 3........C
F002....... 4........
F003....... 1........D
F004....... 1........E
etc

and want to go to:
Unit.........unitCount........WoNum
F001....... 1........A
F001....... 2........SomeText
F002....... 1........B
F002....... 2........
F002....... 3........C
F002....... 4........Another Text
F003....... 1........D
F004....... 1........E
etc

If that is the case... this query will select F001,2 and F002,4 doing the update from there should be a brease

SELECT myTable.Unit, myTable.UnitCount, myTable.WoNum
FROM [SELECT myTable.Unit, Max(myTable.UnitCount) AS MaxOfUnitCount
FROM myTable
GROUP BY myTable.Unit]. AS GetMaxQuery INNER JOIN myTable ON (GetMaxQuery.Unit = myTable.Unit) AND (GetMaxQuery.MaxOfUnitCount = myTable.UnitCount)
WHERE myTable.WoNum Is Null Or myTable.WoNum="";

regards

The mailman