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.