Solved

Excluding zero from Min(field)

Posted on 2014-11-21
11
167 Views
Last Modified: 2014-11-24
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
Comment
Question by:HRISTeam
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 20

Expert Comment

by:dsacker
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];

Open in new window

0
 
LVL 1

Author Comment

by:HRISTeam
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

by:HRISTeam
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 20

Expert Comment

by:dsacker
ID: 40458627
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
 
LVL 35

Expert Comment

by:PatHartman
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

by:
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

by:Dale Fye (Access MVP)
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

by:HRISTeam
ID: 40458672
You have the answer and it works!  and I appreciate it.
0
 
LVL 1

Author Comment

by:HRISTeam
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 35

Expert Comment

by:PatHartman
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

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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question