subtraction question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a inventory table and a customers order table.Is it possible to
reduce the inventory table by the amount a customer orders? I set up a form
from these tables and of course, each time i enter a record , it just adds on
to the DB. I need the inventory to reflect an ongoing total from each order
done.

Thanks
 
Dear JK:

There are some things you should consider:

1. Will it ever happen that an order is corrected in the quantity ordered,
or in the item which is ordered?

2. Will an order ever get cancelled?

Finally, if any of the above happens, do you expect the inventory to track
correctly? Do you have any idea how much programming is involved in the
above possibilities?

Now, there is a classical, well tried and proven way to avoid this problem.
Don't do it.

Instead, use queries to track all the incoming and outgoing stock as
transactions, adding and subtracting quantities. When an order is changed
as described above, it will simply change what is being subtracted. It's
much simpler to implement, and extremely reliable.

In a typical inventory system designed this way, there is no column in any
table showing the quantity on hand.

What there is would be a record showing how many of each item were counted
at the last inventory, followed by add the additions and subtractions since.

Tom Ellison
 
Dear JK:

I'm not sure what you want. An example would be a complete, working
database that is built to custom circumstances. This would be extensive and
would not probably closely match what you need.

I could share a specification of one case, but with similar results.

So, what I'll share is a bit of the process.

There are certain necessary attributes of an inventory transaction. These
must be in the query that provides the list of transactions to the query
that sums up your inventory:

1. a unique key to the item

2. the quantity

3. the date of the transaction

4. information determining whether the quantity adds to, subtracts from, or
replaces the quantity on hand

Other optional information would include a location (in case you need to
track inventory at multiple locations) and how the transaction affects the
general ledger. Transactions from purchases may also contain a dollar
amount. This same sub-system may be the source for cost of sales if needed.

I would draw up a profile for this as though I were designing a table.
Every source of information would need to conform to ability to create the
profile.

This is still rather sketchy, I expect. When designing such a system in
advance of building it, some experience and expertise should be brought to
bear on the information available and the product to be produced to
synthesize a working design, mentally tested against all needs and sources.
But then, that's true for most non-trivial database projects.

Tom Ellison
 
Back
Top