Find duplicates in same table where one of the duplicate records has cost of zero

I have Inventory table, each record is entered as inventory is recieved.  I need to find duplicate Inventory part numbers ONLY where one of the duplicate reocrds has a cost = $0 (zero)

Table is called tblInventory
sample data below.  I want to exclude the part number 0031223413 because both records have a cost greater than zero

Part No                  Descr                                          Date Created     Qty      Cost
001085128584      Axe MUSK 150 ml                       6/25/2014      1       $2.60
001085128584      Axe MUSK 150 ml                       5/8/2014              5      $0.00
0024021201      Camphophenique-Gel .23 oz        6/24/2014      1      $0.00
0024021201      Camphophenique-Gel .23 oz               6/24/2014      5      $0.00
0031223413      Dimetapp-Cold & Cough 4oz               3/31/2014      13      $4.39
0031223413      Dimetapp-Cold & Cough 4oz               3/31/2014      1      $4.39
0031223413      Dimetapp-Cold & Cough 4oz               5/5/2014              1      $4.39
0031223513      Dimetapp-Cold & Allergy 4oz       3/31/2014      23      $0.00
0031223513      Dimetapp-Cold & Allergy 4oz       7/7/2014               6      $4.53
Sheila MurphyAsked:
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.

Helen FeddemaCommented:
Can you be a little more precise about what you want to retrieve?  For example, do you want to find all records with the same inventory part number, where exactly one of the records has a cost of zero?  Or would it be one or more records in the group with a cost of zero?  In the query results, do you want all the records that meet your criteria, or just one record per qualifying inventory part number?
0
Sheila MurphyAuthor Commented:
Thank you for your response:

I want to find all records with the same inventory part numbers, where one or more records in the group with a cost of zero.   I do not want to summarize by Part number to start with.  I know how to do that.
0
PatHartmanCommented:
A simple solution is to create a query that selects records with a cost of 0.  Save that query.  Then create a second query that joins to the 0 cost query to select all non 0 values.

You could also do this with a correlated sub query but I don't like working with those in QBE because they are hard to see  (and not all database engines optimize them properly) so I lean toward the two query method especially since it is easier to test.
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.

Sheila MurphyAuthor Commented:
I am not sure how I would setup Query #2 - "Then create a second query that joins to the 0 cost query to select all non 0 values."
0
Dale FyeCommented:
This query groups on [Part No], counts the number of records for each [Part No] and counts the number of records which have a zero cost, all in one group by query.  And then filters only those records where the [part no] has more than 1 record and at least one record where the [cost] = 0

SELECT tbl_Inventory.[Part No]
, Count(tbl_Inventory.[Part No]) AS RecCount
, Sum(IIf([Cost]=0,1,0)) AS Zeros
FROM tbl_Inventory
GROUP BY tbl_Inventory.[Part No]
HAVING (Count(tbl_Inventory.[Part No])>1)
AND (Sum(IIf([Cost]=0,1,0))>0)
0
Sheila MurphyAuthor Commented:
Thank you - This does not look like a Second query since only 1 table is being used.  Am I correct.  Don't I need to compare Qry1_All Zero against tbl_Invenotry to find the zero duplicates?
0
Dale FyeCommented:
smurphy04,

The query I provided should identify exactly what you asked for, in a single query.  Pat Hartman was recommending two queries, and that is the way I started to go, but believe this will meet your needs.  If you need more fields from tbl_Inventory, you will need to join the query I posted above back to your table to get the additional fields, or do something like:

SELECT tbl_Inventory.*
FROM tbl_Inventory
WHERE tbl_Inventory.[Part No] IN (
SELECT tbl_Inventory.[Part No]
FROM tbl_Inventory
GROUP BY tbl_Inventory.[Part No]
HAVING (Count(tbl_Inventory.[Part No])>1)
AND (Sum(IIf([Cost]=0,1,0))>0)
)
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
Sheila MurphyAuthor Commented:
Excellent, will try later this evening.  I am out of the office right now.
0
Sheila MurphyAuthor Commented:
Dale, I can't get this to work, should I be using tbl_Inventory_1 for the second instance?  I am using Access 2010 query grid - SQL View
SELECT tbl_Inventory.*
FROM tbl_Inventory
WHERE tbl_Inventory.[Part No] IN (
SELECT tbl_Inventory.[Part No]
FROM tbl_Inventory
GROUP BY tbl_Inventory.[Part No]
HAVING (Count(tbl_Inventory.[Part No])>1)
AND (Sum(IIf([Cost]=0,1,0))>0)
)
0
Dale FyeCommented:
by can't get it to work, do you mean you are getting an error, or no records returned?

You could try the following, but what I posted above worked for me in 2007, with your data.

SELECT tbl_Inventory.*
FROM tbl_Inventory
WHERE tbl_Inventory.[Part No] IN (
SELECT I.[Part No]
FROM tbl_Inventory as I
GROUP BY I.[Part No]
HAVING (Count(I.[Part No])>1)
AND (Sum(IIf(I.[Cost]=0,1,0))>0)
)
0
Sheila MurphyAuthor Commented:
Sorry, I missed this reply.  It was a spelling mistake, thank you for your help it was exactly what I needed.
0
Sheila MurphyAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for smurphy04's comment #a40274385

for the following reason:

It was exactly what I needed without creating 2 queries.  He was "spot on!"
0
Dale FyeCommented:
Believe the poster meant to select one of the experts comments, his own.
0
Sheila MurphyAuthor Commented:
Sorry about selecting incorrect comment - Answer was spot on.
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.