Suggesting for Creating an Inventory Database with respect to datahandling

[TylerBennett]

I am currently working on an inventory database for my work.

As i've progressed in developing it I have been thinking about the best method for handling the difference in input and output data to create a 'remaining inventory'

Currently my line of thinking is:
Have a form to receive input data... creating an input data table
Have a form to receive output data... creating an output data table

I can then use a query to isolate various parts (query will show all parts that start with the id '678' which I can then count) and produce a count of each for both my input and output data table

Theoretically I can subtract the count(input) from the count(output) to determine how much is leftover for storage.


Is this the best method for going about an inventory database and determine how much stock is left over after shipping parts out? Any other suggestions that may be a little cleaner would be welcome

[NeoPa]

I see no reason for separate tables to handle the inputs and the outputs. They're all deltas one way or another.

That said, stock control is often handled by having deltas applied to a stock-check value. Each stock check supplies values that override all that went before, but deltas can be applied to stock-check values up to and until the next stock check.

[PhilOfWalton]

I believe the best way is to create transactions against each stock item, and then you have a complete record.

Transaction would have at least
StockID
TransDate
TransQuantity
Other info perhaps like where it was sent

Stock Balance is obviously opening balance + or - Transaction.

Phil

[NeoPa]

Although stock-takes can be entered as an adjustment they can also be set up as a third type of transaction which is an absolute rather than a delta. That way a new absolute value can be added any time so finding the current situation can be done without the necessity to trawl through what may consist of many years of transactions that come in many times each day - adding up to hundreds of thousands for each item. All that's necessary in such a case is to find the latest absolute and any deltas dated after that one.

It's just a short-cut really. Not always necessary, but often more practical than the alternative.

[PhilOfWalton]

Bitter experience tells me a detailed audit trail has it's advantages. As a clothing manufacturer, we kept finding that stock was short. As the audit trail listed every garment sold and to which retailer, when a garment came back with a complaint, and we had never sold that particular style to that retailer, it was not difficult to establish who was doing the stealing.

The stockman & packer had a nice little racket going with one of out reps!!

Phil

[NeoPa]

Hi Phil.

If you look closely you'll see there is no info missing from my suggested scenario.

The two differences are simply :

1. There are extra stock adjustments where necessary.
2. Getting the latest stock levels takes less processing of data.

It also gives the opportunity to look deeper into mismatches as soon as they occur as they are immediately highlighted.

[jforbes]

I've seen ERP systems with transaction adjustments both ways, relative and absolute. Relative is easier to deal with Query wise.

I remember a question like this a while back that I went and found that may be helpful: How do I store Totals in Access using VBA code

[NeoPa]

That looks like a good link JForbes.

It's great when other experts chip in and give a different perspective on things. Thanks to both of you for contributing your experiences.

[TylerBennett]

Yes thank you very much for the input.

Admittedly most of that went over my head (stock check and control) but it gives a path to try to go down if what i'm doing doesn't work.

Phil I like that idea but at the moment the database will be very basic and more or less just be a representation of the items made and moved (not much regard for where it's going).

This was why I have it set up as an input and output table which is run against a query to create a count of each item and now i'm just finding a way to subtract the values.

However if this doesn't work I will be investigating these tips! Thanks again

[NeoPa]

Hi Tyler.

Let me see if I can convince you to merge your tables and to think of deltas (IE Changes.) rather than specific positive (incoming) and negative (outgoing) changes. Humans typically see them as different and opposite, but actually they are basically the same thing - a movement - they just have different directions.

Keeping two separate tables to do fundamentally the same job will only lead to complications for you. It can certainly be made to work, but why complicate your life unnecessarily?

[zmbd]

TylerBennett, I've PM'd you a copy of my "toolbox" therein is a link to a tutorial site that uses an inventory management database as the tutorial. You may want to work through that tutorial in that it will help you wrap your head around database design AND you will have a functional, if somewhat basic, inventory database when done.
Bytes.com\Inbox