DVD Rental Update Stock

[matt925]

Hi,
FAIRLY new to all this.
I'm having a little trouble figuring out how to update the stock levels for items in my stock table. It is a DVD Rental Database, so of course I need stock to be subtracted and added back on.

I already have a query for the amount of stock for each item, however, this only shows how much stock is in the 'AmountStock' column of my stock table (the original amount of stock).

I have a form also linked to my Rental Table, which works fine.
Just can't figure out how to make a query for updating stock each time a film is borrowed and returned!!

Any help would be MUCH appreciated.
Thanks,

 

[Trevor G]

Hi,
FAIRLY new to all this.
I'm having a little trouble figuring out how to update the stock levels for items in my stock table. It is a DVD Rental Database, so of course I need stock to be subtracted and added back on.

I already have a query for the amount of stock for each item, however, this only shows how much stock is in the 'AmountStock' column of my stock table (the original amount of stock).

I have a form also linked to my Rental Table, which works fine.
Just can't figure out how to make a query for updating stock each time a film is borrowed and returned!!

Any help would be MUCH appreciated.
Thanks,

Welcome to the Forum,

You need to look at using an Update Query.

Can you upload a sample of your database, which version of MS Access are you using?

 

[matt925]

Hi, I'm using Access 2007.
Attached a copy

 

[matt925]

Hi there,
Since I uploaded the database I have managed to get a query working for it.

Only thing is.. it deducts the (QUANTITY x HOW MANY TIMES THAT DVD APPEARS ON THE RENTAL TABLE)

Any ideas anyone?
So confusing!!

Help Much appreciated

 

[Trevor G]

Matt no database has been uploaded.

Try doing something like this.

Create a history table, then as each DVD is rented run an Append query to add a copy of the rented DVD information then you can use the History table to give you daily totals and then you can run a query to use the history against the original table and then use calculations using the expression builder to work out stock levels etc.

 

[jdraw]

Here is a free data model of a movie rental business. Perhaps you can glean some info from it.
http://www.databaseanswers.org/data_models/video_rental_store/video_rental_dezign.htm

Do you really have to reduce inventory or do you really just identify a specific (product) DVD as being "rented"??

 

[Galaxiom]

... so of course I need stock to be subtracted and added back on.

No actually.

In a well normalized database, the inventory file does not contain any record of stock quantities. Stock movements such as acquisitions, disposals, rental and return are all recorded as transactions. Summing the transactions reveals the currrent stock on hand.