Checking Appointment Availability

[GrenetS]

I have an 'Appointment' table that includes 'Appointment_Date' and 'Appointment_Time' fields. Each record has an appointment time of either 10:00, 13:00 or 16:00.

I'm trying to make a query that asks the user to input a date, and returns the available appointments for that date - (max 6 appointments per time slot).



I'm learning Access at the moment and have been struggling all day - thanks in advance for any help!

[Bulzie]

Probably going to need more info. Can you us an example of how the data looks in your table. Do you have the 6 appointment fields in the table? So if 2 of the 6 appointments have names in them, you only want to return the other 4 slots? Or you just want to return a count that there are 4 slots available?

[GrenetS]

Thanks for the reply Bulzie.

For my Appointment table, I have around 40 records that include : AppointmentID, Appointment_Date, Appointment_Time, Diagnosis, PatientID (fk), and StaffID (fk). These are just random dates that I entered to have some data in the table, but as I mentioned above, each appointment has an appointment time of 10:00, 13:00 or 16:00.

For each day I want there to be a maximum of 18 appointments (6 for each time slot). So, I want to be able to enter a date to check whether or not these slots are available.

I hope this is a better explanation, thanks again for any help.

[Bulzie]

So in your table, there is not a way to know how many appointments are for a specific time slot. You want to run a query based on the date you enter to see how many appointments (6 max for each time slot) are available for each of the 3 time slots?

[GrenetS]

That's right, I haven't got anything in the table that defines the max amount of appointments, is this a problem? That is what I want the query to do.

[Bulzie]

Well can do a query to show what appointments are recorded on that date for each appointment time. Not sure if that will show you what you need to know. If you see say 2 for 10:00, you know you have 4 more you can book.

So go to query design to create a new query, select the table or drag it into the query, add all the fields into the query. Then in the criteria row for the Appointment_Date column, put [Enter Date] and maybe for the time slot, select the Sort Ascending. then when you run that query it will prompt you for a date, then return all records with that date in it sorted by the time slot.