Solved

# Excluding zero from Min(field)

Posted on 2014-11-21
152 Views
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.
0
Question by:HRISTeam
• 5
• 2
• 2
• +1

LVL 20

Expert Comment

ID: 40458560
Would something like this work:
``````SELECT tblPharmacyDrugs.[PharmacyDrug],
...
Min(tblPharmacyDrugs.[Cost]) AS LowestCost2014,
...
FROM tblPharmacyDrugs
WHERE tblPharmacyDrugs.[Cost] <> 0
GROUP BY tblPharmacyDrugs.[PharmacyDrug];
``````
0

LVL 1

Author Comment

ID: 40458567
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

LVL 1

Author Comment

ID: 40458574
by way of saying "first lowest non-zero", I mean "lowest value" for each drug group (A, B, C...) That's non-zero.
0

LVL 20

Expert Comment

ID: 40458627
Can you say something like:
``````WHERE (tblPharmacyDrugs.[Cost] <> 0 AND year(yourDateValue) = 2013)
OR ( year(yourDateValue) <> 2013 )
``````
... to cover you both ways? Just thinking out loud.
0

LVL 34

Expert Comment

ID: 40458634
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

LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 40458635
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

LVL 47

Expert Comment

ID: 40458639
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

LVL 1

Author Closing Comment

ID: 40458672
You have the answer and it works!  and I appreciate it.
0

LVL 1

Author Comment

ID: 40458684
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

LVL 34

Expert Comment

ID: 40458732
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

LVL 1

Author Comment

ID: 40462078
Thanks for the great information.  Hope you have a very nice Thanksgiving and all.
0

## Featured Post

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…