Using a find duplicates query to find duplicate data between multiple fields

Question

My apologies if this is a very basic question, but I am very new to using Access and database management and design in general.

I work on a survey which requires field interviewers to complete assignments. These assignments are composed of an employee ID, site location, date, and 6 hour time block. For any given assignment, there can be either 1 site location or 2 (where the employee would split their time interval between the two sites). I am interested in running a find duplicates query to find all days within a month where a site is visited more than once.

My data set looks something like this:

SiteID1   SiteID2   Date   StartTime  EndTime EmployeeID
1646      1646      03/11  11:00      17:00   0000
1646      1646      03/03  11:00      17:00   0002
4242      1646      03/19  11:00      17:00   0001
1646      4242      03/11  08:00      14:00   0000

I ran a query using the query wizard to find duplicates within the SiteID1 and SiteID2 fields. It only returns the 1,2 and 4th records. I want it to return the 3rd record as well. How can I change my query so that it will look for duplicates in either the SiteID1 or SiteID2 fields, and not treat them exclusively? I'm actually not even entirely sure why it did not return this record, since it still shares duplicate data in the SiteID2 field.

Thanks in advance.

You want the result grouped by site1+site2? No matter the dates? I.E. if the same id appear more then once in both site1+site2 then return the rows with this id? — sagi
– sagi, Commented Mar 17, 2016 at 12:46
If a duplicate SiteID comes up on the same date, I still want to see those records (such as the case with the assignments on 03/11). Basically, all of these records should be considered duplicates for my purposes. For the 03/19 record the siteIDs are reversed (4242 is 1 and 1646 is 2), but for me this does not matter. It still shares the same siteIDs as all of the rest of the records. — teleost
– teleost, Commented Mar 17, 2016 at 14:21
Say the record for 03/19 had 4242 in SiteID1 and 9999 (arbitrary, just a non-duplicate value) for SiteID2. I would still want this record to be in the result as it shares the same SiteID1 with another record's SiteID2. I'm sorry if I'm not being very clear here. — teleost
– teleost, Commented Mar 17, 2016 at 14:28
Is it all the table that you shown ? you don't have an ID column ? — Thomas G
– Thomas G, Commented Mar 20, 2016 at 20:49
Yes, each assignment (record) has a unique number code (if that is what you are asking). This is only a sample of the table for the sake of explaining what I am trying to achieve. — teleost
– teleost, Commented Mar 28, 2016 at 17:57

Paul Maxwell · Accepted Answer · 2016-03-20 23:08:01Z

0

This may help you arrange the column values to arrive at the summary needed:

SELECT
      IIF( SiteID1 >  SiteID2 , SiteID2, Siteid1)
    , IIF( SiteID1 >= SiteID2 , SiteID1, Siteid2)
    , Date
    , COUNT(*)
FROM Sheet1
GROUP BY
      IIF( SiteID1 >  SiteID2 , SiteID2, Siteid1)
    , IIF( SiteID1 >= SiteID2 , SiteID1, Siteid2)
    , Date
;

The IIF() calls swap over the values is that is needed (e.g. 1646/4242 and 4242/1646 are treated as equal.

You can then use a HAVING clause to locate only those rows that have a count of more than 1 or 2, e.g.

SELECT
      IIF( SiteID1 >  SiteID2 , SiteID2, Siteid1)
    , IIF( SiteID1 >= SiteID2 , SiteID1, Siteid2)
    , Date
FROM Sheet1
GROUP BY
      IIF( SiteID1 >  SiteID2 , SiteID2, Siteid1)
    , IIF( SiteID1 >= SiteID2 , SiteID1, Siteid2)
    , Date
HAVING COUNT(*) > 2
;

NOTE you do NOT have to have to include COUNT(*) as a visible column when using a having clause, but you can do both and that is quite a common thing to do.

As you are new to SQL in general it is worth noting that many other SQL complaint databases would not support IIF() and they would use case expressions instead. Also in many other databases the word date could be a reserved word so you would need to be careful how you use it, like this:

SELECT
      case when SiteID1 >  SiteID2 then SiteID2 else Siteid1 end
    , case when SiteID1 >= SiteID2 then SiteID1 else Siteid2 end
    , "Date"
FROM Sheet1
GROUP BY
      case when SiteID1 >  SiteID2 then SiteID2 else Siteid1 end
    , case when SiteID1 >= SiteID2 then SiteID1 else Siteid2 end
    , "Date"
HAVING COUNT(*) > 2
;

I would suggest you don't use the word date as a column name, make it more meaningful e.g. AssignmentDate

edited Mar 20, 2016 at 23:08

answered Mar 20, 2016 at 22:14

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

teleost Over a year ago

I've tried this but unfortunately I just don't understand what it has achieved. Again, I apologize for having so little knowledge of Access and SQL and being slow to understand. From what I can see it has rearranged the data, but not in a particularly helpful way. It has arranged the records in order from lowest number Site ID to highest number Site ID. I posted this in a previous comment, but I've found that this stackoverflow.com/questions/30758307/… is very similar to my situation, but I am having trouble adapting the code to work in Access.

Collectives™ on Stack Overflow

Using a find duplicates query to find duplicate data between multiple fields

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related