Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Unanswered: finding duplicates

    I am fairly new at Access. I am working on creating a database that records Employees, the consumers they worked with, and the date their work was billed for.

    I want to set up a query to find all records where the consumer are the same, the work date is the same, but the billling date is NOT the same.

    Can anyone help with this?

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The same as what?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2010
    Posts
    24
    I think he wants to find records with matching attributes?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I correctly understand your question, it should be:
    Code:
    SELECT Tbl_CF_Data2.Employee_ID, 
           Tbl_CF_Data2.Consumer_ID, 
           Tbl_CF_Data2.Work_Date, 
           Tbl_CF_Data2.Billing_Date
    FROM Tbl_CF_Data2
    WHERE (Tbl_CF_Data2.Consumer_ID IN 
           (SELECT [Consumer_ID] 
              FROM [Tbl_CF_Data2] As Tmp 
              GROUP BY [Consumer_ID],
                       [Work_Date],
                       [Billing_Date] 
              HAVING Count(*)>1 AND 
                     [Work_Date] = [Tbl_CF_Data2].[Work_Date] AND 
                     [Billing_Date] <> [Tbl_CF_Data2].[Billing_Date])
          )
    ORDER BY Tbl_CF_Data2.Employee_ID, 
             Tbl_CF_Data2.Consumer_ID, 
             Tbl_CF_Data2.Work_Date;
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •