Excluding zero from Min(field)

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.
LVL 1
HRISTeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Would something like this work:
SELECT tblPharmacyDrugs.[PharmacyDrug],
...
Min(tblPharmacyDrugs.[Cost]) AS LowestCost2014, 
...
FROM tblPharmacyDrugs
WHERE tblPharmacyDrugs.[Cost] <> 0
GROUP BY tblPharmacyDrugs.[PharmacyDrug];

Open in new window

0
HRISTeamAuthor Commented:
I actuallly have to place it in the select line, because in addition to 2013, the years 2014, 2015 need to be available, therefore, I can't filter out using a WHERE clause because it will remove rows containing other years.  I'm just looking for something that can be part of the select query where it takes Min to first lowest non-zero value.
0
HRISTeamAuthor Commented:
by way of saying "first lowest non-zero", I mean "lowest value" for each drug group (A, B, C...) That's non-zero.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dsackerContract ERP Admin/ConsultantCommented:
Can you say something like:
WHERE (tblPharmacyDrugs.[Cost] <> 0 AND year(yourDateValue) = 2013)
OR ( year(yourDateValue) <> 2013 )

Open in new window

... to cover you both ways? Just thinking out loud.
0
PatHartmanCommented:
The problem is caused by using 0 as the default for the cost field.  The default should be null.  Some versions of Access make the intelligent default of null but others use 0.  

To fix the problem,
1. change the default to null
2. run an update query to change all the 0's to null

Then the query could be:
Select Year(YourDate) As PurchaseYear, Min(Cost) as MinCost, Max(Cost) As MaxCost
From YourTable
Group by Year(YourDate);
0
Dale FyeCommented:
You might want to try:

Min(IIF(NZ([tblPharmacyDrugs.[Cost], 0) = 0, 999, tblPharmacyDrugs.[Cost])) AS LowestCost2014,

That should effectively replace NULLs and zeros with the value 999, which I hope is far greater than your minimum.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Oops, since this is a query, the NZ( ) function will probably return a string, so you might want to try adding the VAL( ) function in there to make sure you are finding the minimum of a numeric value, and not a string.

But I do like Pat's suggestion, if you can implement that.

Min(Val(IIF(NZ([tblPharmacyDrugs.[Cost], 0) = 0, 999, tblPharmacyDrugs.[Cost]))) AS LowestCost2014,
0
HRISTeamAuthor Commented:
You have the answer and it works!  and I appreciate it.
0
HRISTeamAuthor Commented:
Dale, Thanks again for the code (actually thanks to everyone)  My only question is a syntax question.  What's the purpose for the 999.  I changed this to 999999999 and it still keeps the same format.
0
PatHartmanCommented:
The purpose is to guess at a number that is probably higher than the real lowest price since the function has to return something.  So the IIf() turns 0 to 999 and hopefully that will be higher than any other value in the set.  However, using this method, if you happened to have a set of records that contained only 0 values, 999 would be returned as the Min value.  That's the downside of this method.

There is another thing you should be aware of and that is that you can't actually get an average if you don't get rid of the meaningless zeros.  Consider:

The average of 3, 0, 3 is 2
But the average of 3, null, 3 is 3.
If you think 2 is correct, leave the zeros alone.
If you think 3 is correct, get rid of them as I suggested.

Query functions ignore nulls.
The downside of nulls is that you have to be aware of the possibility and consider them when doing arithmetic operations or using certain types of selection criteria.  So if you are adding two columns which might contain nulls, you need to use the Nz() function.  cola + colB will return null if either is null so you need to use Nz(cola, 0) + Nz(colB, 0) to get a value that is not null.

In a query, if you have a field that has 4 values - a, b, c, null and you want to return everything except c, you need to decide if you want to include nulls or not:
Where fldA <> "c" - will return a, and b only but not null
Where fldA <> "c" or fldA Is Null will return a, b, and null
0
HRISTeamAuthor Commented:
Thanks for the great information.  Hope you have a very nice Thanksgiving and all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.