triple bookings

[lydz]

triple bookings *DCount question*

im making a system and part of that system is the booking of the library

2 classes can be in the library at once
i want a way where if a 3rd teacher tries to book a slot then theyll be told its not available
if only one class was allowed itd be easy, by using a compound key
but cos 2 classes are allowed this is a bit hard for me lol
trying to think of a way to allow 2 bookings but not more
any ideas?
thanks

 

[Fifty2One]

just add 2 fields to the db would do the trick if you code the form to book the session to accept
the first booking if no one is booked
the second booking if the first is booked
display a 'time slot not available' message if both fields are booked

 

[lydz]

ok thanks will think about how to do that
cheers

 

[neileg]

Use DCount() to count the number of bookings already.

 

[Dennisk]

Adding an extra two fields in the database creates an un-normlalised table.
What would happen if it was decided to increase the number of courses to 20 or 100. This would require a major amendments to forms/queries and reports. A database should be flexible enough to cope with this.

by the way is this a single or multi user system?

 

[haze]

I'm not an expert and maybe i'm missing something here but here you go anyway

Maybe it would be easier if you added two library locations in your classrooms/class locations table, like "Library section A" and "Library section B". That way if one is full you would have to try the other library location. I (don't think?) this is a bad denormalization because if I remember correctly school library bookings almost always in my experience have special designations for each section booked, ie. section A only has access to the computer area, section B only has access to the worktable area, etc. and essentially you were booking different locations or different privileges within the library.

Even if that's not the case, it just might be easier this way if this is the only time it happens. I dunno, something to think about

 

[The_Doc_Man]

If the locations table includes a date and a code (such as A or B) AND the date and location code are parts of a compound prime key, this is NOT denormalized. Because in this case, the open slot is part of the key.

It does not matter that physically there is only one space. Logically, if there are two spaces / slots, then the BUSINESS MODEL says there are two slots.

So have a table that contains a list of the various places you can hold these sessions INCLUDING the code for the number of slots.

Now your bookings are like a junction table that includes the date and location codes (and the latter includes the slot number.)

When you search for conflicts, you search the junction table. Now if you find only one conflict but there is a second slot for that location, you can make the booking. If you find both slots conflicted, you are done.

If the slot number is NOT part of the prime key of the locations table, then you do, indeed, have a denormalized table.

NOTE for those who want to consider more than one site: If you have location codes that include "slots" for up to X different sessions at once, this always works - even for small venues where X = 1.

 

[lydz]

hey thanks everyone for your replies i was just coming back here to ask something else
it was funnish to see the person above suggest DCount, cos thats what im lookin at right now
it looks the best way really, normalisation, tho i dont really understand it is pretty important and its all one massive room

so basically i have the DCount code, its just the criteria part i dont really get

this is what i have atm
If DCount("[Date] & [Period]", "tblRIC1Bookings", "[Date] = [Date] & [Period] = [Period]") > 2 Then
MsgBox ("Sorry, the RIC is full up at your requested period and date.")

its probably obvious from that i dont get the criteria lol, its meant to be checking whether the date and period entered have been before
theres allowed to be 2 classes in there at once
but not more thats the whole >2
cheers