Andy wrote:
Hi folks
I teach. At school, four IT rooms are booked using a paper based outline
timetable. Completing it is easy but basic and impossible to ensure
completion of all fields (name, year group, subject and software) and
analysis of bookings is a nightmare.
I just fancied pottering with Access, which I am pretty familiar with, to
see if I could create a means of booking a room using a database. Locking a
booking would then be a problem to overcome and creating a means to analyse
the bookings should be easy.
I've scanned the internet for resources and ideas and have come up blank.
Anyway here have any ideas?
Appreciatingly yours :-)
Andy
This is just a general outline of a start.
Create a Names or Student table.
Create a Software table
Create a Subject table.
Create a rooms table if you think you will expand the room count.
Create a booking table. This table would have a booking id (primary
key), the nameid, the software id, the subject id, the date, the room
number, the time start, the time end.
Create a table to hold the room number, the booking table id, and time
increment.
Create a form to enter software using Software table
Create a form to enter subject using subject table
Make the subject form a subform of the software table (I assume you have
multiple subjects for each software piece)
Create a booking form. Use a combo dropdown where you can add a student
on the fly or select an existing student. Use a combo dropdown to
select the software. Create a combo to select the subject. Requery the
subject combo when the software is selected. Create fields to enter the
date.
If the user has entered all of the data up to this point in the booking
form, all that is need is to select the room and enter the start and
ending times. But you don't want a conflict in book times so you need
to see what times are available for each room for that day. And this is
the tricky part.
I would probably use a listbox to display the room numbers and another
listbox to present the current bookings. When a room number is
displayed, the bookings for that day for that room would be displayed.
The listbox for bookings would display the time and the person. I would
create a temp table. It would have a record for each time increment
8am, 8:30am...4:30pm . The person could click the (usually empty) time
slots to be filled in for the person. Once the times are selected, a
save button is pressed. The program would then scan the listbox and
create records in a schedule table that has the same structure as the
temp table.
You would need to be proficient in list boxes to use this method. ANd
you would need to permit deletions (this would remove the booking record
and all times assigned to the booking). You might have assigned a
person to 8am-9am but if you click the 8:30am row, the other person
would now have the 8-8:30 slot. IOW, deleting and updating will be your
PITAs.
I have no idea how proficient you are with Access. It may be beyond
your skill set. If you can make it up to the point before I mentioned
the list boxes, write back for more suggestions.
