Results 1 to 9 of 9
  1. #1
    AndyJMiles is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    UK
    Posts
    5

    Compare each record in a table with all other records to detect duplication with complex criteria

    As per the title, I want to compare each record in an appointments table with all other records in that table to detect duplication, using complex criteria.

    The SQL statement for the criteria is as follows-

    WHERE

    [Date] = [Tables_Appointments].[Date] ----date is the same----

    AND

    ( [Staff-Name] = [Tables_Appointments].[Staff-Name] OR [Consulting-Room] = [Tables_Appointments].[Consulting-Room]) ----either room or staff the same

    AND

    ( [StartTime] Between [Tables_Appointments]![StartTime] And [Tables_Appointments]![EndTime] OR [EndTime] Between [Tables_Appointments]![StartTime] And [Tables_Appointments]![EndTime]) ----there is any overlap between start and end times for a consultation appointment----

    So, if there is a time overlap, on the same date, for either the same room, or the same member of staff, it will be flagged up as an error.

    This seems logically, and syntactically correct, in itself, but I am at a loss as to how to get Access to perform this test for each record in a table, compared with every other record in the table.



    Anyone know how to do this? My current query brings up all records in the table.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Something like this perhaps. Note date is a reserved word and should not be used as a field name and using non alphanumeric characters can cause unexpected issues

    SELECT *

    FROM [Tables_Appointments] A INNER JOIN
    [Tables_Appointments] B ON A.Date=B.Date

    WHERE (A.
    [Staff-Name] = B.[Staff-Name] OR A.[Consulting-Room] = B.[Consulting-Room])

    AND A.[StartTime]<=B.EndTime AND A.EndTime>=B.StartTime

    AND A.PK<>B.PK

    You need the last criteria otherwise the A alias will always return itself from the B alias

  3. #3
    AndyJMiles is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    UK
    Posts
    5

    Thumbs up

    Quote Originally Posted by Ajax View Post
    Something like this perhaps. Note date is a reserved word and should not be used as a field name and using non alphanumeric characters can cause unexpected issues

    SELECT *

    FROM [Tables_Appointments] A INNER JOIN
    [Tables_Appointments] B ON A.Date=B.Date

    WHERE (A.
    [Staff-Name] = B.[Staff-Name] OR A.[Consulting-Room] = B.[Consulting-Room])

    AND A.[StartTime]<=B.EndTime AND A.EndTime>=B.StartTime

    AND A.PK<>B.PK

    You need the last criteria otherwise the A alias will always return itself from the B alias
    Thanks for that - will try it out tomorrow. Is "PK" just a meaningless string or does it refer to anything specific

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is "PK" just a meaningless string or does it refer to anything specific
    it's the unique ID for the record - known as a primary key. It is usually an autonumber and should have no meaning other than uniquely identifying the record.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    The best solution will be, when you can create an additional unique index (not PK) which uses same conditions you listed. Then you don't have to code anything - Access simply doesn't insert any double entries for this index.

  6. #6
    AndyJMiles is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    UK
    Posts
    5

    Thumbs up Success

    Quote Originally Posted by Ajax View Post
    it's the unique ID for the record - known as a primary key. It is usually an autonumber and should have no meaning other than uniquely identifying the record.
    Yes - sorry - I'm familiar with Primary Key, which, in this case is an autonumber vale for the Appointment record number.

    I tried it, and it works fine, though I will follow your advice and change field names, though have had no trouble from them to date.

    So - thanks very much for that. I will also check out the "create an index" suggestion from the other contributor.

    My only remaining consternation is that I do not understand exactly how, or why, it works, so will need to do some reading up.

  7. #7
    AndyJMiles is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    UK
    Posts
    5
    Thanks - The query suggested by Ajax works, but will look into this idea too.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    AndyJMiles is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    UK
    Posts
    5

    Thanks

    Thanks for all the help with this. I have decided to go with the query, and report, rather than the index. Blocking all apparent duplicates would not be appropriate, because of some fuzziness in the data. The report allows people to decide what, if anything, needs changing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populating records based on complex criteria
    By Antonio in forum Queries
    Replies: 5
    Last Post: 05-16-2018, 08:23 AM
  2. Replies: 10
    Last Post: 05-17-2017, 02:06 PM
  3. Replies: 0
    Last Post: 06-22-2016, 01:33 PM
  4. Replies: 3
    Last Post: 01-06-2016, 07:42 PM
  5. Replies: 4
    Last Post: 05-14-2014, 10:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums