Avoid double bookings in simple database (1 Viewer)

[carlgoro]

Hello,


I work across a number of small venues which have art cases that can be booked for displays. I am trying to build a simple data base to report what space is available and also what art is currently being displayed. The art is usually booked by month, but sometime it can be booked for a week etc.

I have set up 3 tables

Art Inventory

Art Cases by Venue

Art Case Bookings

In the art case booking form, I have set up the start and end date but I cannot figure out how to avoid double bookings of a case? Once I have that worked out I believe I know how to build the required reports for my needs.

I know there are number of other posts about this sort of database but I am self taught and get totally lost when people start posting code. I've attached a copy of my database to this thread.. if anyone could help that would be amazing!!

thank you
Francis

 

[jdraw]

Can you step back, and in simple logical terms, identify what makes a double booking?

Let's consider Art Case 999. Suppose Art Case 999 is booked for July 4, 2013 to July 10, 2013 at Miami, FL (venue).

So if you have a request for Art Case 999 for July 6,2013 ---sorry already Booked.

Is that the issue you're facing in extremely simple terms?

If so, create a TableX to include
ArtCaseId
, BookedDateFrom
BookedTo ( or NumberContiguousDays) 'you need to know/calculate the end date

When you go to make a proposed Booking,
check the ArtCaseId, and
see if the proposed Booking Date is Between BookedDateFrom and BookedTo

If there is no such Booking Already, you can proceed with Booking.
If There is a conflict, you would be double booking -so don't proceed with that Booking.
Hope this helps.

I'm sure there are other factors such as moving/transporting exhibits from one venue to another; setting up/taking down exhibits; staff holidays/non-availability..

These should probably be entered into your BookingsTable as some sort of adminBooking-- just so you don't doublebook over these as well.

EDIT NOTE: I wrote the above before opening your database.

But now I have opened the database and find that:

Your Tables are not defined properly in my view. You have not clearly identified Entities involved, so tables are not defined.

Your tables Art Case Bookings is really info that could best handled via a query, if you had defined tblVenues and possibly Locations.

Also, it seems that an ArtCase is composed of 1 or More ArtIds
An ArtCase can be Booked at A Venue_Location.
A ArtCaseBooking can be for 1 or more contiguous days.
Your ArtInventory consists of a number of ArtIds.
A Venue consists of 1 or more Locations??

For your own info and reference, you should define ArtInv, ArtCase, Venue, Location, Booking.. You'll be surprised at what forcing yourself to write a clear definition does -- it really clarifies some thinking.

I would recommend ArtId as autonumber to uniquely identify a "piece of Art" -- somehow Art_Inv doesn't indicate a piece of Art --but you know the business and I don't.

All of your data deals with 1 venue BOND?? I recommend a Venue table, if these are know in advance.

A Booking would involve an
ArtCase,Venue, Location,StartDate and Duration (possibly Clients/sponsors...)
A Venue would consist of 1 or more Locations (need more info)???

I see something along these lines for tables.

ArtItem
ArtId
ArtName
ArtDescription
other info specific to the item

ArtCase 'contains 1 or many ArtItems
ArtCaseId
ArtId
ArtCaseName

Venue
VenueID
VenueName

Location
LocationID
LocationName
LocationDescription

Booking
BookingId
ArtCaseId
VenueId
LocationId
BookingStartDate
BookingDuration

where
ArtCaseId
VenueId
LocationId
BookingStartDate would for a composite unique index to handle duplicates

This construct would prevent duplicates at the data base level.

Just some thoughts after looking at the database.

Main concern : table design and no relationships in the current database.

I recommend you read and work through this tutorial to better understand Table, table design and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck with your project.

 

[carlgoro]

Thank you so much for your reply. That is exactly the logic I am looking for.

I have the start and end date in the form but I don't know how to do the checking? Is there a formula i can enter?

Cheers
Carly

 

[pbaldy]

This should help with the second step jdraw mentioned:

http://www.baldyweb.com/OverLap.htm

 

[jdraw]

Thank you so much for your reply. That is exactly the logic I am looking for.

I have the start and end date in the form but I don't know how to do the checking? Is there a formula i can enter?

Cheers
Carly

I recommend you read and work through the tutorial I suggested at the bottom of post #2. The issue is table and relationships, not formula at the moment.

PBaldy's overlap material is exactly the set up to determine availability for Booking.