Using Date and Time

[reginaldmerritt]

I'm in the process of planning out a database that can organise our staff bookings. Basically having the ability to link staff to certain clients at chosen periods of time.

For example StaffMemberA may be booked to with ClientA on 01/01/09 from 08:00 to 20:00. This data could be held in the table tbBookings with Primary keys StaffMemberName and Date.

What i want the program to do is to tell the user if StaffMemberA is already booked in the time selected. This could be done by looking at tbBookings to see if StaffMemberA with Date is there. But how do i relay that StaffMember is available from 00:00 to 08:00 or 20:00 to 00:00. In other words tell what hours StaffMemberA can be booked so that a double booking does not arise.

I was planning to create a new table from any bookings made that hold the Date, StaffMemberName and a Boolean field for each time segment from 00:00 to 00:30 and so on till 24:30. So the program can look at this table to tell if a StaffMember is available on any given day. But I thought there must be an easier way to do this rather than creating a whole new table.

Any help or suggestions are welcome. Thanks

[Rabbit]

There's no simple way to do this with a standard query but a VBA function can return a string specifying the times available by looping through the records that are not available.

[reginaldmerritt]

There's no simple way to do this with a standard query but a VBA function can return a string specifying the times available by looping through the records that are not available.

I think realy it depends on how i can get the program to interprate the two fields with the time FROM and TO. Say FROM = 8:00 and TO = 20:00 i need to represent that as a block of time some how, perhaps with an array? ummm..

Anyway i guess i'll have to keep at if there is no simple way to do it. Thank you for your help.

[Rabbit]

Like you said in your first post, you can make a new table with all the available times or you're looking at some VBA coding. Do you have any experience with VBA?

[Rabbit]

Resuscribing .

[reginaldmerritt]

Done some basic stuff VB not really worked with databases before. I was going to create a form to hold the data i wanted to write to a new table. If you have some advice i would be appreciated, i'm not expecting you to have to go in to to much detail.

[Rabbit]

Let me know if you understand this:

This is the description of a function that will return a string with the available times for a chosen person on a chosen day.

1) Create a recordset that pulls all records for one person for one day ordering by the time.

2) If the record count is larger than zero. Loop until you hit the end of the recordset.

3) Starting with 00:00, the start time of the record in the recordset is the end time of the free period. The start time of the next free period is set to the end time of the record in the recordset.

4) At end of recordset the end time of the last free period is 23:59.

Throughout, you will be concatenating into a string that will be returned by the function.

[reginaldmerritt]

thanks for getting back to me,sorry that i've taken so long to reply.

I think i understand, that defintly makes more programming sence than using an array. Not quite sure on the syntax but i can look that up, i understand what your getting at.

Thank you, i'll give it try.

[Rabbit]

Good luck. Let us know if you run into any roadblocks.