Stock Control Questions

[Edgar Thoemmes]

Hi

I am currently helping set up a database for a Rare Book Dealer. My first
initiative in designing this database is the stock control issue.

The Dealer buys in books from a variety of sources and will keep them in
stock for sometimes long periods of time, generally the Dealer will only have
1 copy of a book in stock but sometimes he will buy a bulk lot of books in so
there may be several copies of the same book. All stock is stored in a set
location identified by a certain code.

I have read a couple of posts and articles describing how Stock Control
should be address in Access (ie not storing physical quantities of stock but
using a transaction table instead and calculating the stock count from there.)

I have a couple of questions regarding this issue. Do you think that I
should use this method for my database considering that most books will only
have one copy in stock? How would I store the Stock Location in the
database...bearing in mind that the Dealer may have for example 3 copies of a
book in Location A12 and 1 copy in location B26?

Can anyone help with my questions above or know of any other further
resources or Sample Database's I could look at?

Thanks

Edgar

 

[Lynn Trapp]

If you haven't done so already, you might take a look at the inventory
control database at microsoft.com:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT061995421033

 

[Brendan Reynolds]

It looks as though you'll need three tables to model the
multiple-copies-at-different-locations scenario. Let's call them Books,
Copies, and Locations. Books will store the information that applies to all
copies of a book, such as title, publisher, ISBN. Copies will store the
information that applies to a specific copy of a book, such as its location,
its condition, and probably its price (assuming that two copies of the same
book may sell for different prices if one is in better condition than the
other). Relationships would be one Book, many Copies, and one Location, many
Copies (assuming that a Location is a section of shelving containing several
physical books).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.