Availability during a period

[Iris]

I am btrying to put together a database that will track
rental and how many additional copies I have available

Order table has the item to be rented and a start and end
period. inventory table as the items and how many of
each. I need to know how many of the items I still have
available. so if an item is rented for 3 days I reduce
it from inventory on all 3 days (not just the start).

I have created a cross-tab query that tells me how many
are rented. however, it is only taking to account the
first day (start-of-rental) and does not take into
account the days between start-of-rental and end-of-
rental.

do you unerstand what I am trying to do? can anyone help

 

[ChrisJ]

From a practical point of view, you are going to end up
with false data. As it turns out, the practical solution
is much simpler.
By pre-logging returns, you will have false data whenever
a return is late, and as you are working in day "lumps",
the data could be wrong for at least part of the return
day.
Have you considered having a "total stock" field to record
how many you own, and a "current stock" field to record
how many are "on the shelf". The "current stock" value
could be decreased as an item is rented, and increased
when an item is returned - both real time.

 

[Guest]

sounds good. I already have a total stock field. How do
I automatically maintain a current stock field? without
having someone constantly update it? or is it even
possible?

 

[ChrisJ]

I guess that depends on your existing stock handling
procedures. You were already planning to enter a whole
bunch of data when a rental went out. It seems to me that
recording the return would not be overly onerous. If you
employ barcoding and had a dedicated PC to handle returns
it could be even easier.

 

[Allen Browne]

The simplest approach might be to calculate the stock level at the time you
need it.

See:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html