Populate a table with date and times

SteveL13 used Ask the Experts™
I have a table named tblPotentialAppointmentDatesAndTimes.  In that table are three fields...  AppointmentID (the primary key) and Date and Time.  When I click a button on a form I want the table to fill with records based on the number of doctors in another table named tblDoctors.  The field have to populate with a date/time every 15 minutes starting at 08:00 am and going through 05:00 pm

In other words like this:

 Date Time Example
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

You can use a loop in VBA or a template schedule table.

It depends on your actual problem, but it sounds like you should use a template schedule table.


What is a template schedule table?
John TsioumprisSoftware & Systems Engineer
Public Function Createapp(inputdate As Date)
    Dim startTime As Date
    Dim endTime As Date
    Dim currentTime As Date
    startTime = "08:00 AM"
    endTime = "05:00 PM"
    Dim rstDoctors As DAO.Recordset
    Dim rstAppointments As DAO.Recordset
    Set rstDoctors = CurrentDb.OpenRecordset("SELECT * From Doctors ") 'I assume you have a table with the Active Doctors
    Set rstAppointments = CurrentDb.OpenRecordset("tblPotentialAppointmentDatesAndTimes")
    With rstDoctors
        While Not .EOF
        currentTime = startTime
                rstAppointments.Fields("appdate") = inputdate  ' Date is a reserved word....make sure you change it to e.g. appDate
                rstAppointments.Fields("appTime") = currentTime
                currentTime = DateAdd("n", 15, currentTime)
            Loop Until currentTime > endTime
    End With
End Function

Open in new window

Distinguished Expert 2017
I'm sure it seems easier to you to think about something that looks like an actual calendar but that is wasteful in the world of relational databases and limits your flexibility.  What happens if a doctor leaves and is replaced by a different doctor?  Do you contact all the patients and give them the option of choosing a different doctor or do you just arbitrarily replace doctorA with doctorB in the schedule records.  What happens if you add evening hours on certain days?  What happens if you get an additional doctor?  How far in advance should you create empty records?

You might want to think about only filling slots for which you have actual appointments.  You can see a schedule by using a left join to join the filled schedule table to the template table (one row per timeslot per weekday where the patientID is null.  You would use criteria such as 12/1/19 - 12/31/19 to see just the work days in December.  If the patient wants a specific weekday, add that criteria,  If the patient wants a specific doctor, you may need to use a different template if a certain doctor only works on tues and thurs.

There are lots of complications in laying out this type of schedule.  I'd actually look for a product to purchase before setting out to reinvent the wheel.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial