Solved

Excluding zero from Min(field)

Posted on 2014-11-21
11
158 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
 
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 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now