Adding things up in inventory

[Thorns]

Hi guys! I'm hoping one of you has an answer for me on this...
My inventory database is set up now except for one thing. If someone wants to enter a certain book and they have 20 copies of it, how can I put that in so that there isn't a new entry for every one of them? It needs to either add itself up or hopefully do it when I search.
Any help would be great.
Thanks.

Thorns

Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[fredk]

Not sure how you set up your database but you would want to have a field for the number of books instead of having duplicate entries for the same book

Fred

 

[Thorns]

Ok I guess what I'm seeing here is that I can have a field named quantity...and I'll just have to tell the woman who will be using this database to make sure that when she adds a book that's already in there she does the math herself and changes the amount of them?

Thorns

Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[fredk]

EIther that or you can have another table linked to the book table - Perhaps called "stock" or something like that - What you could do in that table is have fields like:

Date in Inventory
Date sold

to name just a few -

If you needed to know how many of the book you have in stock, you would run a query of only those that do not have a sold date - or perhaps you also add a "status" field in the stock table with options like "instock" and "sold" - then you would run the query on all that are "instock" to get the number of books.

I think that is how I would approach it. What you would do is set up a form with the main book table (which has all of the data about the book) and add a sub-form that is for the "stock" table - the user can pull up the book information on the main form and the sub-form will populate with all of the activity in the stock table - therefore, they just have to update the stock form with the info on the new stock

Hope that helps!!

Fred

 

[fredk]

One more thing regarding my proposed "stock" table - you would have to use a field to link the stock table to the original book table - that will be the key to making it work

Fred