By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Suggesting for Creating an Inventory Database with respect to datahandling

P: 23
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
Apr 25 '17 #1

✓ answered by 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

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 25 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
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
Apr 27 '17 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 27 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
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
Apr 27 '17 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 29 '17 #6

jforbes
Expert 100+
P: 1,107
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
Apr 29 '17 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 30 '17 #8

P: 23
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
May 2 '17 #9

NeoPa
Expert Mod 15k+
P: 31,494
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?
May 2 '17 #10

zmbd
Expert Mod 5K+
P: 5,397
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
May 13 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.