• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Find duplicates by comparing 5 fields in a query

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
Sashaski
Asked:
Sashaski
  • 2
  • 2
1 Solution
 
dqmqCommented:
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
 
dqmqCommented:
pls ignore residual select at end of prev answer
0
 
SashaskiAuthor Commented:
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
 
SashaskiAuthor Commented:
My bad, that worked! Thank you so much!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now