Solved

# Excluding zero from Min(field)

Posted on 2014-11-21

I have a problem in that I'm trying to get the minumum cost of a group of pharmacy drugs (the Maximum works fine). For example, in 2014, I obtained certain drugs by month. Not all drugs are bought each month; therefore, there is a "zero" cost involved. I also don't have the same number of drug records because some drugs may have started later in the year; hence the different number of rows per Drug A, B, C,... Here is a sample list:

PharmacyDrug Cost

Drug A $15.15

Drug A $14.75

Drug A $14.22

Drug B $6.20

Drug B $6.20

Drug B $7.00

Drug B $6.80

Drug C $0.00

Drug C $22.00

Drug C $18.00

Drug D $0.00

Drug D $210.00

Drug E $38.50

Drug E $41.20

Drug E $40.60

Drug F $15.00

Drug F $18.00

Drug F $0.00

Drug F $16.00

Drug F $17.00

Based on this information, here is what I get...

PharmacyDrug Min Max

Drug A 14.22 15.15

Drug B 6.2 7

Drug C 0 22

Drug D 0 210

Drug E 38.5 41.2

Drug F 0 18

However, I don't want to show the minimum as 0 if I have another "real" value (This would be the next lowest above 0). I want the Minumum value to show the lowest "non-zero" value. Basically, this is what I'm trying to show in the above table sample:

PharmacyDrug Min Max

Drug A 14.22 15.15

Drug B 6.2 7

Drug C 18 22

Drug D 210 210

Drug E 38.5 41.2

Drug F 15 18

Notice that Drug C, D, and F do not show 0 as the minumum, but instead shows the lowest of the remaining values that are not 0. I'm bascially using the following line as part of my main SELECT query to extract the MIN() value:

SELECT tblPharmacyDrugs.[PharmacyDrug],

...

Min(tblPharmacyDrugs.[Cost]) AS LowestCost2014,

...

FROM tblPharmacyDrugs

GROUP BY tblPharmacyDrugs.[PharmacyDrug];

Any assistance with this will be greatly appreciated.