Query to see if a client was seen by two providers on the same day.

[mzaroff]

Hello,



I have the following four fields (plus many others) in a table: ClientID ProviderID Dateof ProcID

I need a query the reveals if a client (ClientID) was seen by two different providers (ProviderID) on the same date (Dateof). It is a large table with more than 300k entries so ordering by client then date and running through it visually is too much. If they were seen by two different providers on the same date then I need to list that information including the procedures that occurred (ProcID) so corrections can be made to a billing code for billing purposes.

Thanks,
Mark

[June7]

The following should return all client records where any client has more than one record on any date. Unfortunately it won't limit to just the dates with multiple providers.
SELECT * FROM tablename WHERE ClientID IN (SELECT ClientID FROM tablename GROUP BY ClientID, DateOf WHERE Count("*") > 1);

Instead, do aggregate query:
SELECT ClientID, DateOf FROM tablename GROUP BY ClientID, DateOf WHERE Count("*") > 1
In another query join that query to the original table and link on the ClientID and DateOf fields.

Another approach (assuming ClientID is number type):
SELECT * FROM tablename WHERE DCount("*","tablename","ClientID=" & [ClientID] & " AND DateOf=#" & [DateOf] & "#")>1;