Lending Library and Reservation System - Avoiding Double Bookings

[Lay-nuh Bigsby]

I created a database off of the Lending Library template for a lending library I own. I am a VERY basic user and have been stumbling trying to figure things out as I go. However, I desperately need to figure out the reservations portion as I'm afraid I'm going to have double bookings on some of my items.

I basically need an automated way to let me know how many of that item is available for the date range I am processing. Right now, I enter each reservation (see reservation screen shot) THEN I have to go to the inventory entry (see screen shot) and manually check to see if the item has a conflict. I'm basically doing double the work which is VERY timely (especially since I only have time to work on this during the evening).

I don't have a clue what the calculation should be (or maybe it's a script?) - I'm sure it's probably something easy.

I need the unavailable dates to pull from the "off-the-shelf" and "restock" date (these are my buffer dates which is typically 2 days before/after the actual reservations).

ANY help is greatly appreciated. I DID look at this post: http://fmforums.com/forum/topic/62390-calculation-or-script-to-avoid-double-or-repeated-datarecords/page__p__294980#entry294980 but in all honesty, it made no sense to me.

[comment]

I'm sure it's probably something easy.

I don't think it's easy at all. In fact, it's more complicated than the problem in the thread you linked to. They only wanted to detect conflicting bookings - you want to count them.

Suppose item X is booked from Monday till Wednesday and again from Thursday till Friday. Now there is a new request to book the item from Tuesday till Thursday. This request conflicts with both previous bookings - but 2 units of item X are sufficient to satisfy all 3 bookings.

OTOH, item Y is booked from Monday till Wednesday, from Wednesday till Friday and from Tuesday till Thursday. The number of conflicting bookings is the same - but you will need 3 units of item Y to satisfy the bookings.