Logic question on an access application

[coyote]

Hi guys, am in a dilemma
Am designing a video/DVD sale and rental application in ms access 2003.
My question is this
I have for example a DVD of 24 season 1 which has 10 copies in the store.
To avoid repetition and redundancy am just assigning a product code to the product and a field Qty in my table takes the quantity which is 10.
My problem is this I would like to label each copy with a unique no. Short of entering each copy which would create repetition I cant seem to overcome this problem.
All help appreciated

 

[DCrake]

Simple Software Solutions

You may have ten copies but when you rent one out how are you going to know which copy was rented out? Which are already out? Who has got them? When are they due back? To avoid duplicate entry you will need to have a 1:m table for each and every dvd even if there is only one copy so that the main description is in the parent table and the individual copies are in the child table. Then do your transactions from the child table.

CodeMaster:

 

[coyote]

How is this possible plus there is a barcode field for these movies
Plus how do I determine my quantity in stock if i have two different tables for the same movies

 

[ErikSnoek]

How is this possible plus there is a barcode field for these movies
Plus how do I determine my quantity in stock if i have two different tables for the same movies

It's easy; One table has the main information like genre and title, the other table (child table) has information like the barcode and some yes/no field in which you store wether or not the dvd has been rented. The relationship between these two tables can be done by adding the unique identifier from the main record to the child record. Or you could make a table storing the identifier of the main record and the child record.
You can determine the quantity in stock by counting how many child records each dvd has. You can even choose if you want to count the number of rented out dvd's by just counting the records that have the "rented" field set on yes.

Hope this makes sense