Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Find duplicates by comparing 5 fields in a query

Posted on 2011-09-13
4
Medium Priority
?
242 Views
Last Modified: 2012-05-12
Hi experts!  

I am working in Access 2010.

I have a query that has these fields: (please see screen shot).

The goal is to compare records in this query based on four fields and dump the duplicate record based on the value of a fifth field:

If ALL of the below four fields are identical....

[RequestDate]
[Company]
[Product]
[Quantity]

then....
the fifth field
[Source]
determines which record will be dropped and which record will stay of the duplicate records.

The [Source] field will always have one of two entries: Susie OR FMS.  FMS should be the winner in all duplicate records.

I hope there is a simple way to solve this!

Thank you.
Untitled.png
0
Comment
Question by:Sashaski
[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
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36532052
Delete * from yourtable T1
  where T1.Source <> "FMS"
  and exists
     (Select * from yourtable T2 where
             t1.RequestDate = t2.requestdate
       and t1.Company = t2.company
       and t1.Product = T2.product
       and t1.Quantity = T2.quantity
       and t2.Source = "FMS"
      )




Select ID from yourtable where source <> "FMS"
0
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 36532061
pls ignore residual select at end of prev answer
0
 

Author Comment

by:Sashaski
ID: 36532236
Thank you dgmg...I tried the code above, it filtered my query to just show the "FMS" records.  I dropped the last select statement. so the code is:


Delete * from yourtable T1
  where T1.Source <> "FMS"
  and exists
     (Select * from yourtable T2 where
             t1.RequestDate = t2.requestdate
       and t1.Company = t2.company
       and t1.Product = T2.product
       and t1.Quantity = T2.quantity
       and t2.Source = "FMS"
      )
0
 

Author Comment

by:Sashaski
ID: 36532247
My bad, that worked! Thank you so much!

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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