Bus Bookings Project

[John_H0ward]

Hi All,
I work as a receptionist and as it has quietened down for the holiday break I thought I would tackle an ongoing issues in my workplace, bookings for our courtesy bus. As I have dabbled in MS Access in high school, some 6 years ago, I thought I would use this opportunity to create a database program to solve the bus issue and relearn a skill.



I have access to our member database which includes member ID, names, addressees, contact numbers etc, everything needed. I have created a table with the members details, simple enough. I then created bookings table which will use Members ID as the relationship.

In the end I would like the user input to include 2 combo boxes which are reliant on the previous entrant. As the bus only runs on certain days at certain times, the first input will be a date field, eg 25/12/16. From this I need the first combo box to show what routes are available on that day.

For example, Route 1 drop off is only available Mon - Fri but Route 1 pick up is available Mon - Sat.

Here is my first problem, I have no idea how to relate a date (25/12/16) to a day (Monday), then I guess I would use a query to determine what inputs are available for the first combo box.

To make matters more complicated only certain times to schedule the bus are available dependent on the day and which route. So the second combo box will need to show what times are available based on the date chosen and then route chosen from the first combo box.

So my first questions are;
Is there a sample project that does something similar already? I checked the sample area but couldn't find one.
How would a combo box have conditional inputs based on a date input field that is converted to a day?
How should I lay out the data for what date and times the routes are available?

I understand this is a lot to ask so any help at all would be very appreciated

Thanks a heap

[ranman256]

You'd have a tVehicle table,all buses to use.
a sub table to this is tVehicleTimes, would hold days,routs each vehicle is available.
VEH,DAY,ROUTE
bus1,Tues,A
bus1,Wed,A
bus2,Mon,B

And a tCalendar table to show the bookings,
VEH,BookDate,ROUTE,ClientId, DriverID
bus1,1/1/17,A,123,101

This data would be used to fill the combo box to allow what's available on a day.
This table can be used to fill a traditional calendar view

[orange]

I haven't seen a data model or data base for your specific requirement. Here is a model for Bus Tours
at Barry Williams' site. Here are some facts that go with the model.

His models are generic in that they deal with most "thins" associated with a subject/business. They may be too specific or too generic for your needs. You can pick the parts you feel are relevant and discard or edit/adjust the rest or add more of your own. His models are meant as a starting point for reference.

My advice is to write a description of what you are trying to do in simple English. You can add details to clarify certain parts if necessary.

Here are a couple of tutorials from RogersAccessLibrary that you should work through to re-familiarize yourself with the design process. Each tutorial will take 30-45 minutes(since you have some experience).
You will learn by working through the process, and what you learn can be used with any data base.
Getting your tables and relationships designed and tested to meet your requirements is the most critical part of database.
ER Diagramming
Class Info System

Here are some other links that you may find useful.

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model


Good luck.

[John_H0ward]

Thanks for your help
I'll be spending the next few days going over the info you've given me.