This is a tough one....

[Guest]

Greetings to all. I have a query question that I need some help with,
please. I have a table with data like this:

Part# Unit Cost AvailableQty
___________________________________________________
123 25.0 0
123 50.0 1
125 12.0 1

What I am trying to do is filter this data down so that for each part
number, the query disregards a record if the available qty = 0. Then, if
there is more than one instance of a part number remaining after filtering
for zeros, to take the higher of the unit costs. This doesn't seem all that
difficult, but I have been racking my brain on it for a while. Any help is
greatly appreciated. Thanks.

 

[Guest]

have you tried a sum query using max?

 

[Guest]

Try this:

make a query with fields "Part#", "UnitCost", "AvailableQty"
enable Totals
set "Part#" to Group By
set "UnitCost" to Max
set "AvailableQty" criteria to <>0


HTH
Joel