The Dreaded Stock Control Database!

[Sam_Bur]

I have started making a stock control database, i have spent about a week on it, And i am struggling! There are obviously lots of different ways to actually build them efficiently. My problem is that i want to add stock and take stock out, changing the stock quantity and obviously keeping track of stock. I only want to ammend the stock quantity, in the stock table if the item is not a new item. The stock is added via a log sheet which could also have a new item. My head is beginning to hurt, but with a bit of help from you guys i might be able to crack it.

The Tables I have are:

tblStock

tblCategories

tblSuppliers

tblInventory transaction

tblStock_in

tblStock_Out

tblEmployees

Is this sufficient? Can i add stock and take stock out with one transaction table? Or would i need to add another table? I believe the code would be through a query but again am a little unsure as never done anything like this!

Please help!!

 

[edtab]

It is possible to use just one transaction table to record all activities
relating to specific inventory items. A simple table would contain the following fields:

InventoryID
TransactionDate
TransactionType
In
Out

You'll need the transaction type to indicate the nature of the "Ins" and
the "Outs". For example, ins may include purchase of inventory, inventory
transfers (from one part to another), inventory adjustments for physical
count discrepancies, and so forth and so on.

The same is true for the outs, one regular entry here would be the sale
of inventory, however, there are other transactions that would be recorded
as outs such as inventory writeoffs, or inventory used for other business reasons,
such as complimentary give-aways, etc.

Keeping the ins and outs in the same table would allow you to easily verify the
inventory balance in your inventory table by simly adding the ins and outs and
subtracting one from the other. Any discrepancy would suggest some possible
error in record keeping.

There may be other suggestions regarding this topic. Keep checking.

 

[AccessNewbie72]

Hmmm I'm interested in this too.

Just wondering, do you have a lot of inventory to track?

I'm playing with my wine collection at home and I created an Access inventory list for it (can we say Access/Wine geek combo?)...I added a 'location' column also so I know where a particular bottle is located and I update the table through form.

I'm just a basic access user and the wine list isn't extensive (40 bottles) so it's ok for what it is, but I would imagine you'd need something that's more user friendly.

It's almost like you need a (yes/no) field in your table for "New Item?" and you create a separate query to double check (with a 'no' in the criteria or something) and update your table accordingly.

Not sure if this helps? I'd like to know your progress also.

Thanks!

 

[The_Doc_Man]

Your best bet here is to learn quickly how to use the forum's SEARCH function and then search for topics such as Inventory and "On Hand" and such terms as that. We have had some vigorous threads on the subject in the past several months.

 

[Sam_Bur]

Cheers guys,

All that tread reading has certainly enlightened me. Now this may be a stupid question, but would i hold the total quantity of the product within the Stock table? And then just update that from the transaction table through the stock_in and stock_out. Any suggestions would be great..

 

[neileg]

All that tread reading has certainly enlightened me. Now this may be a stupid question, but would i hold the total quantity of the product within the Stock table? And then just update that from the transaction table through the stock_in and stock_out. Any suggestions would be great..

No! Good database design dictates that you don't store the result of a calculation.

The balance on hand needs to be calculated from the receipts and issues.

While I'm posting, there's no need to think of stock in and stock out as different in nature, it's just the sign that matters. So you only have one field for a stock movement value which would be +ve for stock in and -ve for stock out. Then you just have to do a simple sum to find the current balance.

 

[Sam_Bur]

thats brilliant so the table set up would be like this...

Then there would be a function in a form connected to the sock_calc table that would calculate the current level of stock?

 

[OllyK]

I think the suggestion is that you don't have the stock calc table. By recording that dates that there is incoming or outgoing stock you can calculate on the fly the stock level for any given date.

 

[The_Doc_Man]

What some people do if their stock transactions are in two tables with two different layouts is to build a UNION query that contains an express for the quantities. For stock OUT transactions (sales, shrinkage, etc.) you have a minus sign in the expression. For stock IN transactions (incoming shipments, returns, etc.), you have a plus sign in the expression. The UNION query allows you to "unify" the disparate records of your transaction tables.

Now just write a SUMMATION query of that UNION query, group by stock number, order by date, and there is your inventory on hand.

Wrinkles: If the transaction tables get TOO big, you can archive some things. One or more of the threads discuss the "on-hand-at-date" transaction, which you synthesize as the summary of ALL transactions prior to date/time X, after which you ARCHIVE (&REMOVE) all transactions prior to date/time X. Thus leaving the on-hand-at-date as a single transaction that summarizes everything prior to that date but only taking up one record. If you take this approach, remember to include your "on-hand-at-date" in the UNION query and remember that its date has to be the date of the cutoff, not the date of the archiving step itself.