Timetable in Access?

[smspeace]

Dear All,
I'm new to this site, but already it looks very helpful, so i'm going to try and test all those great minds out there. I'm trying to create an automated timetable booking system in Access. The basic outline of the project is to allow staff to book rooms in a building through a database. I'm having GREAT trouble trying to a)Normalise the data and b) Present it in a "Timetable" format. There are 4 rooms and 11 periods in each day. So far i've got 4 tables;

DEPARTMENTS(Department_ID, Department_Name,
Department_TelephoneNo)
STAFF(Staff_ID, Staff_MemberName, Department_ID)
ROOMS(Room_ID, Room_Name, Number_PCs, Whiteboard,
Interactive_Whiteboard, Projection_Equipment,
Television/Video, Scanner, Colour_Printer,
LaserBW_Printer)
BOOKINGS(Week_No, Day, Period, Room_ID, Staff_ID)

It seems to me to have a lot of repeating fields (namely Week_No, Day etc..) and i'm finding it almost impossible to create a "Timetable" form to display the information for the week (Pivot Table is the closest if have come). Can anyone pass a little help my way? Any feedback would be greatly appreciated!

Ali

 

[Maquis]

Your tables look normalized to me. The only possible suggestion I have is do you need Week_No and Day? Would a single date/time field work instead?

You can then create a form that is tied to the bookings table with some Combo boxes for users to select their Id's (or read it from their login id), room and period. Allow them to select a date and start period and end period for the booking. (You may want to change the booking table to include a start period and end period. Or you could just create multiple records in the bookings table for multi period bookings.)

Another thought is a preview form showing all the rooms available with the room "perks" and available times. They could choose a room from this form and have the room form then call the booking form.

That should get you started. Good Luck! Maq B-)
<insert witty signature here>

 

[ZylaWonder]

smspeace:
Your are very correct in recognizing that correct table design (normalization) is the key to making your database work correctly. Next step after normalization is relationships. [Departments]![Department_ID]as a primary key looks properly normalized to [Staff]![Department_ID]. It looks as if you want to know what department a staff member belongs to or what members a department contains. I think I see a normalization problem with Rooms and Bookings. I do not see a relationship between Rooms and Bookings. The Rooms table contains only details (attributes) that each room contains, which look OK. The Bookings table seems to be the problem. Rooms is to Booking as Department is to Staff. You want to know which Rooms have been booked and by which Staff member and on what day. So..I would create Booking as follows:

Room_ID
Date
Period
Staff_ID

If each room has 4 periods for each day then it will require 3 fields to create a primary key. Room_ID;Date;Period. The Staff_ID is simply an attribute of the Primary Key. Create a relationship between Rooms and Booking in the relationships window using Room_ID as the linking field. Now the Rooms table is simply a lookup for the actual booking table. You can't book a room that first does not exist in the Rooms table. Now if you create a main form select a room from a drop down list and the main form would show all of the attributes of the selected room. Create a sub-form linked to the main form or parent by the sub-form or child Room_ID. Base the subform on the Booking table. Now you can see what dates have been booked for a paticular room and by which Staff member.

 

[smspeace]

Thankyou all for your help. I've continued to chop and change the timetable database, and now i think i might have it working.