[morne.lippiatt]

Hi,

I am running Microsoft Access 2007 on Windows 7. I want to create a scheduling tool in MS Access 2007 but I am at a loss for the table structure. I have a bunch of trainers and want to be able to log their availability and then schedule them for work but have the database tell me when they are already scheduled or not available.

I am an intermediate user of Access so understand most of the tools. I have tried having tables with the master dates, and dates available and dates scheduled and have used update and delete queries that run on a macro to update and alert schedules but this seems like a needlessly complex way of structuring it and not very flexible.

Perhaps a DBA out there could give me a few pointers on the best table, query, macro combination that would be easy to scale up or down. Preferably something that does not require VBA coding (or if it must, very simple coding).

Thanks in advance

Sincerely

Morne

[zhouma]

Hello Morne,

I'd like to help get the discussion going...here are the tables I think you'll definitely need

Table 1
Field 1- TrainersID Number
Field 2- Trainers Name (this can be split into 2 fields for first name and last name or remain one field)

Table 2
Field 1- TrainersID Number (so that you can link table 1 & 2)
Field 2- schedule definition (now this would be up to you but some text like the following choices: "part time, full time, weekender"

Table 3 (optional)
Field 1 Name -Part time
Records- What part time means...could be related to number of hours worked or days worked

Table 4 and 5 (also optional) to define weekender and full time

Now here's where I'm sketchy...the table to help define your trainer's availability. Part of me thinks a form that queries the trainer's ID, name, whether they are part time, etc. and then gives you the ability to hour by hour put whether they are available/unavailable based on their schedule. Through this you should be able to get a query that says when your trainers are and aren't available. From there you can use VBA coding or macros to do prompts from when yourself or someone else tries to schedule a trainer for a time they are unavailable.

Anyone else with any other ideas?

Best,
Zhouma

[OBP]

I have a "Scheduling" type database (originally for Doctor's Patients" that you can have to see how it all works.

[morne.lippiatt]

OBP,

That would be great if I could have a look. I learn best by example so if you already have one I would love to see it.

Regards

Morne

[OBP]

This schedule is for every 15 minutes per day from 8.45am to 7.30pm, 7 days a week for a year.
I can obviously adjust the time periods and overall time per day.
You select a Doctor and then select a patien per time slot.

[morne.lippiatt]

OBP,

This is great thank you! This will be a great input form for setting the appointments/schedule.

Regards