Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17
  1. #16
    hispeaches's Avatar
    hispeaches is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    12
    I found that if I join date and time on both tables then I get the exact time match, however, I need everything that is within the hour. in other words, if the appointment at the UE was at 07:15 I would need that to come up in the query because it's within the 07:00 hour.

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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??
    Click image for larger version. 

Name:	DateTimeHourIntervalMatchOct3018.PNG 
Views:	12 
Size:	88.8 KB 
ID:	37116


    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
    Click image for larger version. 

Name:	CountVisitsGroupBYTimeIntervalOct302018.PNG 
Views:	11 
Size:	21.2 KB 
ID:	37118

    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.
    Attached Files Attached Files
    Last edited by orange; 01-28-2019 at 09:38 AM.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-14-2015, 04:26 PM
  2. Replies: 2
    Last Post: 10-07-2015, 12:28 PM
  3. Replies: 3
    Last Post: 04-24-2012, 08:32 AM
  4. Replies: 34
    Last Post: 02-15-2012, 05:17 AM
  5. Replies: 12
    Last Post: 05-22-2011, 03:49 PM

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