Query\db design help

[BRZ-Ryan]

I am working on a scheduling DB where I need to provide selection of available time slots. I first have the user select a start-time, and based on the start-time I want it to display available end-times (using a listbox for each). Using 30 minute increments.



Example scenario: If there was an appt scheduled already in the db from 10-11 and the user selected 9:30 as start-time, it should show no available end-times since the first available end-time would be 10:00, but that is already booked so no results are shown. If the user selected 11 as a start-time, then it should show 11:30,12:00,12:30 and so on, until the next start-time is found in the table which would block it from being an available end-time. The time-range I am working with is 7:00am-5:00pm.

I am currently recording the times in a table (ID,Date,TimeSlot), with date being the date and TimeSlot being the start-time with an understood 30 minute increment. So if they select 10 as start, 12 as end, I am writing to the table: Date/10:00 ; Date/10:30 ; Date/11:00 ; Date/11:30

Is it possible to do a dlookup on each row returned from a query? I was thinking of querying my start-times table and with each row returned, do a dlookup() on my appts table to see if that date/time-slot exists as being booked, if not, confirm it is > starttime and if so, return it to end-times listbox.

Anyone have any ideas\comments?

[June7]

You might find this thread of interest https://www.accessforums.net/access/...69/index2.html

[BRZ-Ryan]

I used your example in that thread as a guide in creating my existing db, however it currently allows selection of 30 minute slots which can be cumbersome. We would rather allow selection of larger slots based on a selected start and end time.

[BRZ-Ryan]

My idea for completing this with VBA:

Assuming a clean table with no entries...loop from the selected start-time to end-time (both lists are full since no existing records), writing each bracket to the table..so 8-10 would be 8:00,8:30,9:00,9:30.

Then to determine potential end-times on future scheduling: User selects an available start-time, query times table and find time slots that do not exist in the scheduled table..load these times into a VB array. Loop through array starting at time values > selected start-time, check if the time-value is within 30 minutes from next time value in array, if so it's a potential end-time. If it's not within 30-minutes, exit loop and no more end-times to show since there is a gap of already-selected time.

I'd prefer to use access functionality as much as possible but don't know if this is possible using only querie(s).

[June7]

Why couldn't 10:00 be an available end time for 9:30? Why couldn't there be an appointment for 9:30-10:00?

Don't understand your last post. Your first shows 30-minute increments.

Here's some more for you to review.

http://office.microsoft.com/en-us/te...001000405.aspx

https://www.accessforums.net/databas...ses-18459.html

http://access-scheduler.com/

http://www.microsoftaccessexpert.com...-Database.aspx

[BRZ-Ryan]

It can be, I don't know where I indicated 9:30-10:00 is not a valid appointment.

The times table has times all time brackets from 7am to 5pm, at 30 minute increments.

On form load, query to populate start-time listbox to find slots in which there is atleast 30 minutes of available time after the start-time (basically all slots that do not exist in scheduled table will work). When a start-time is selected, populate end-time listbox; find times > start-time that have no gaps (already selected time) stopping when an existing scheduled time bracket is hit, otherwise up until 5pm.

Thank you for the links

[June7]

I am confused by the second paragraph in your first post.

If you have adapted code for your requirements, what's wrong? If you don't want 30-minute increments, change the code appropriately.

[BRZ-Ryan]

Oh I am sorry, you are right, 10:00 would be an available end-time.

I have not adapted it, I have the idea but implementing is what I am trying to figure out..my current one is based off of what you put together in that other thread. One of the things we dont like is if the user wants to schedule 8-10, he will have 4 separate slot listings (one for each 30-min slot). These scheduled times then appear in a scheduled listbox with 4 individual slots to encompass the 2 hours. I'd prefer it to have 1 listing in the scheduled listbox for 8-10. I'm afraid people will over-look what's available and whats not (ie. multi-selecting 9:00/9:30/11:00, when 10:00/10:30 are taken) assuming they have that full 2.5 hours. I believe I can handle it but it will be mostly VBA which I try not to do. Though, being it's not very data-intensive I don't think it would hurt performance or anything.

[ItsMe]

Maybe have two fields in a single record that represents an appointment. One field/column for the start time and the other for the end time. Another table (tblTimeSlots) would list all possible times in 15 minute increments. tblTimeSlots would not get edited, it will act as a list. An additional table would act as a junction table.

A query could look at the start and end time for an appointment. It could then determine which records in tblTimeSlots fall within the range and append the junction table with time slot PK's along with the appointment PK.

[BRZ-Ryan]

I think I will use the indexes for all calculations instead of time values, it will be a lot easier to work with integers 1-20 than messing with time values.

[ItsMe]

Maybe. You will know when you get to your SQL. Just make sure you store your times in your tables as Date/Time data type.

[BRZ-Ryan]

Well I'm not well versed in SQL but I am in VB so I may read values from table into an array then do the calculations manually.

[ItsMe]

I would imagine you will want to manipulate the RowSource of your combos and RecordSource of a subform. You could create a saved query object and then include that in a simple SELECT SQL string that includes a criteria clause. This way you don't have to get all crazy with joins. Let the query object do the heavy lifting and you just basically add a WHERE clause or better yet a BETWEEN clause. Easy to do because there are examples all over.

Regardless, you will have to build the tables and get some sample data in them before you can create your queries and forms. Then you can worry about how. Right now I am not envisioning all the details.

[BRZ-Ryan]

Yes I currently set the row source of the list similar to what June did in his example. Sample data is in place already, going to work in it tomorrow.