RE: Normalised Stock Table

Hello TonyT,

First I think that you're going to have to noodle on a couple of foundation
items, with respect to the issue at hand, what is your mission for the
database?

With respect to "bought and sold in quantities" are you out to track
inventory and transactions based on the little items, or are you treating
"packages" as entities? If it's the former, then a 6 pack of spark plugs is
really just 6 individual spark plugs. If it's the latter, then the "6 pack"
is the inventoried entity, which will have a different part number than the
individual spark plugs, and any conversion between the two (packs vs.
individuals) will be a transaction ("creating" one, "destroying" the other)
which needs to be tracked.

Next you have to define your mission in detail with respect to transaction
tracking and inventory tracking of individualized/serialized items and items
which are not so. I know that you know your general wish in this area, but
you'll need to get specific. Here's my guess at an answer: For part number
which has been identified as one which requires individualized/serialized
tracking, cause all transactions and tracking to occur at the individualized
level.

Assuming that my guessed answer is right, your dilemma isn't one of not
knowing structure, it's more fundamental, that a table (be it inventory or
transactions) really should have only one "what is a record" definition,
whereas you have two fundamentally different ones (using inventory table[s]
as an example):

1. Record is a description of the part and a numerical inventory summary
2. Record is an instance of stocking of on (individualized) item.

One way to reconcile the two is to use a standard inventory transaction
application (like Allen's) except to consider the "identity" of an item to be
a concentation of it's part number and serial number. When there is no
serial number, then the part number alone will identify the item. Move
your inventory count data into what you now consider to be your "serial
number" table, but this is now your inventory table; the serial number field
will be blank on those items that do not get individualized tracking. Your
old main table now becomes just a static table with data on basic part
numbers. Add a "RequiresIndividualTracking" field to tag a part number as
such. Such would trigger a few things, including the serial number field
becoming a "must enter", and transactions could only be quantity 1 etc.
Looking up the (composite) part number would be required for a transaction.
So, an addition of a serialized item would always be a "no record found" (=
new record required) or you could have it automatically jump to that
whenever adding a individualized item.

Just an idea plus a few thoughts.


Fred


.

Relevant Pages

  • RE: Normalised Stock Table
    ... The current version is an Invoicing & stock control system set up to handle ... tracking and inventory tracking of individualized/serialized items and items ... cause all transactions and tracking to occur at the individualized ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Inventory
    ... You can do an update query to adjust the quantity value in your inventory ... bill should probably include the ability to have a qty for each bill item. ... link to the Bill of Materials table to release those items from inventory. ... > I need it to subtract from my Inventory Transactions table per the example ...
    (microsoft.public.access.tablesdbdesign)
  • Re: maintaining on-hand balances in an inventory
    ... You can drop the trigger idea and drop the inventory level from the item ... I believe keeping the quantity on hand in the item master is normal ... let's assume that your transactions table is as follows ...
    (microsoft.public.sqlserver.programming)
  • Re: Inventory-Am I on wrong path?
    ... Josh, if you can calculate the inventory, there is absolutely no need to ... and NOT store the dependent value. ... You probably do need a way to handle transactions a bit more than you have. ... This is for receiving only. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How would you...
    ... I think I'm going to call in a local company to provide a little one-on-one ... Thank you again - it is so great to have this resource of knowledge! ... for anything that will add or deduct from inventory, ... Then, if you decide to "know" inventory based on transactions, you need to ...
    (microsoft.public.access.tablesdbdesign)
Quantcast