Find the most!

M

marzinp

Hi,

I have a big Products database (ProductN°, Price, Brand,
BooleanMostExp). I'd like to find the most expensive product from each
brand and update BooleanMostExp accordingly. How can I do this? Nothing
I've tried works...

Thanks a lot!
 
J

John Vinson

Hi,

I have a big Products database (ProductN°, Price, Brand,
BooleanMostExp). I'd like to find the most expensive product from each
brand and update BooleanMostExp accordingly. How can I do this? Nothing
I've tried works...

Thanks a lot!
Click to expand...

I'd suggest that the field BooleanMostExp *should not exist*: any
price change in any record in the table has the potential to make that
field invalid both for the changed record and for the formerly most
expensive item!

The dilemma (if you want to do this anyway) is that you need a Totals
operation to identify the most expensive, and no Totals query is ever
updateable. I'd try an Update query using the DMax() function instead:

UPDATE Products
SET BooleanMostExp = True
WHERE [Price] =
DMax("[Price]", "Products", "[Brand] = """ & [Brand & """");

Of course if there are ties, all of the tied items will be marked.


John W. Vinson[MVP]
 
Top