?
Solved

Excluding zero from Min(field)

Posted on 2014-11-21
11
Medium Priority
?
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 38

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 48

Accepted Solution

by:
Dale Fye earned 2000 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 48

Expert Comment

by:Dale Fye
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 38

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…
Suggested Courses

765 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