Stock Control Database Problem

[p5x]

I'm setting up a database to keep track of book/video loans. Trouble is that some items have more than 1 copy and it'd be a waste of time and space to enter the data for each item 2-5 times.
I was thinking of having a drop down menu labelled "copies" for each item, so the user selects how many copies they have of each item and when a certain item is loaned out it will show up the number of copies in stock in the info section of each particular item.

Basically it will subtract 1 copy from the original amount when a item is loaned out and then add 1 copy back when that item is brought back...is there an easy way of doing this?

 

[WayneRyan]

p5x,

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=38832&highlight=loan

Wayne

 

[p5x]

thanks, i'd already seen that thread so i'm now thinking of having unique ref's for each item-eg-if item 2 has 3 copies it will have a id of 002-a, 002-b, 002-c...i'm not sure exactly how to implement this without duplicating data though.
Also, is it possible to add a prefix to the item id depending on what is selected in a drop down menu when adding an item to the db. eg-if video is selected it will make the item id-V002 but if book is selected it will make it B002.