Availability during a period

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

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.

-----Original Message-----
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
.

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?
-----Original Message-----
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.

-----Original Message-----
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
.

.

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.

-----Original Message-----
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?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iris" wrote in message
...
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