Query to find duplicate appointments

[medosol]

Clients are defined as Client_ID, Appointment date is Date, physician is Resource_ID and APPT_UNIQUE_ID is a unique ID assiged to each appointment. I am trying to select a list of Client_IDs that have appointments with more than once Resource_ID on the same date but ignore Client_IDs if the second appointment on a given date is with the same resource

Running the Access Find Duplicates query wizard for Client_ID, Date and Resource_ID gave me this SQL code

SELECT CLIENT_ID, DATE, RESOURCE_ID, APPT_UNIQUE_I
FROM Appointments WHERE CLIENT_ID IN (SELECT CLIENT_ID FROM Appointments As Tmp
GROUP BY CLIENT_ID,DATE,RESOURCE_ID HAVING Count(*)>1
AND DATE = Appointments.DATE And RESOURCE_ID = Appointments.RESOURCE_ID)
ORDER BY CLIENT_ID, DATE, RESOURCE_ID

The only problem is that it returns clients with >1 appointment with the same resource. I don't care about that. What I need is a variation on this that will only show clients that are scheduled for more than one resource on the same date and ignore clients that have >1 appointment on the same date if it is with the SAME resource

Please send sample SELECT for SQL server as I plan to use a pass through query. Thank you very much

 

[Michel Walsh]

Hi,


Make a first query that removes the duplicated values:

SELECT ClientID, [Date], Ressource_ID
FROM appointments
GROUP BY ClientID, [Date], Ressource_ID


save it, say, under the name DistinctApp.


Use that query instead of Appointments in the sub-select query.



Hoping it may help,
Vanderghast, Access MVP

Clients are defined as Client_ID, Appointment date is Date, physician is

Resource_ID and APPT_UNIQUE_ID is a unique ID assiged to each appointment. I
am trying to select a list of Client_IDs that have appointments with more
than once Resource_ID on the same date but ignore Client_IDs if the second
appointment on a given date is with the same resource.

Running the Access Find Duplicates query wizard for Client_ID, Date and

Resource_ID gave me this SQL code:

SELECT CLIENT_ID, DATE, RESOURCE_ID, APPT_UNIQUE_ID
FROM Appointments WHERE CLIENT_ID IN (SELECT CLIENT_ID FROM Appointments As Tmp
GROUP BY CLIENT_ID,DATE,RESOURCE_ID HAVING Count(*)>1
AND DATE = Appointments.DATE And RESOURCE_ID = Appointments.RESOURCE_ID)
ORDER BY CLIENT_ID, DATE, RESOURCE_ID;

The only problem is that it returns clients with >1 appointment with the

same resource. I don't care about that. What I need is a variation on this
that will only show clients that are scheduled for more than one resource on
the same date and ignore clients that have >1 appointment on the same date
if it is with the SAME resource.