By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Using Date and Time

reginaldmerritt
100+
P: 201
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
Sep 4 '07 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,396
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.
Sep 4 '07 #2

reginaldmerritt
100+
P: 201
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.
Sep 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,396
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?
Sep 5 '07 #4

Rabbit
Expert Mod 10K+
P: 12,396
Resuscribing .
Sep 5 '07 #5

reginaldmerritt
100+
P: 201
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.
Sep 6 '07 #6

Rabbit
Expert Mod 10K+
P: 12,396
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.
Sep 6 '07 #7

reginaldmerritt
100+
P: 201
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.
Sep 12 '07 #8

Rabbit
Expert Mod 10K+
P: 12,396
Good luck. Let us know if you run into any roadblocks.
Sep 12 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.