Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • 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
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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