[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-18
14
Medium Priority
?
238 Views
Last Modified: 2014-08-23
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
0
Comment
Question by:Sheila Murphy
14 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40268702
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
 

Author Comment

by:Sheila Murphy
ID: 40268713
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40268831
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:Sheila Murphy
ID: 40268850
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
 
LVL 49

Expert Comment

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

Author Comment

by:Sheila Murphy
ID: 40269006
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
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40269030
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
 

Author Comment

by:Sheila Murphy
ID: 40269043
Excellent, will try later this evening.  I am out of the office right now.
0
 

Author Comment

by:Sheila Murphy
ID: 40274385
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
 
LVL 49

Expert Comment

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

Author Comment

by:Sheila Murphy
ID: 40279833
Sorry, I missed this reply.  It was a spelling mistake, thank you for your help it was exactly what I needed.
0
 

Author Comment

by:Sheila Murphy
ID: 40280396
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40280397
Believe the poster meant to select one of the experts comments, his own.
0
 

Author Closing Comment

by:Sheila Murphy
ID: 40280959
Sorry about selecting incorrect comment - Answer was spot on.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

834 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