Can you show a specific example from your data? Pick a location and whatever details you need and walk through the process (in plain English) that you are trying output.
I am not following your comment because I don't understand how (the logic) that would say this appt should have been at 7:00 at another location??? What location? What determines that a Patient who visited UE should have gone to ServArea X at some time slot? Other than date and Time, I'm not following how the tables should/could be related. Seems we are missing something??
I have made copies of your tables and created a query to show visits where the data and time match the Hour Limits in the ScheduleTable.
I also have found that combining Date and Time into a single field may add to some complexity/confusion.
This is current query
Code:
SELECT UE.VisitDtTm
, UE.PCPphysgroup
, SCH.SchedApptDtTm
, SCH.[Service Area]
FROM CopyOfCMGpatientListJED UE, CopyOfT_ScheduleUtilizationJED SCH
where
DateValue(UE.VisitDtTm) = DateValue(SCH.SchedApptDtTm) AND
TimeValue(UE.VisitDtTm)
BETWEEN TimeValue(SCH.SchedApptDtTm) AND DateAdd("n",59,TimeValue(SCH.SchedApptDtTm))
This is a sample of output. Does it make any sense??? What next??
Update:
I then used this query CountUEVisitsBySchedDateTimeSlot on the above query
Code:
SELECT DateTimeMatchTestJED.SchedApptDtTm
, Count(DateTimeMatchTestJED.VisitDtTm) AS CountOfVisitDtTm
FROM DateTimeMatchTestJED
GROUP BY DateTimeMatchTestJED.SchedApptDtTm;
to get the Count of Visits at UE grouped by the Date and Hour Intervals in schedule.
This is result for Oct30 2018
Is this part of what you need??
I "played" with your data in a copy of the database which I attached in a zip. Not sure of your exact requirement, but hopefully
this is helpful to you. There is a word doc overviewing what was done.