I thought tblQtyOnHand kept a fixed figure of quantities and thus
thought
that would be a breech (I Had been reading Allen Brownes page
http://allenbrowne.com/AppInventory.html
cheers,
Kenny
:
Ken,
Thanks for the additional information. For the small operation you are
describing, you can get by with much less than I originally gave you.
I
put
the whole thing together not knowing the details of your business. You
can
probablly just go down to the Detail Level on the Ordering system --
as
you
had it -- just add a field for the Quantity_Received, so you can know
how
much is outstanding on the order. You can probably also combine all
the
Receiving and Inventory functionality into one set of tables --
InventoryTransactions and QtyOnHand. You might even be able to put
them
into
one table. Basically, what you need is a table that continually tells
you
how much you have in your storeroom. It will need to be updated each
time
an
item is received and/or delivered to it's final destination.
I'm not quite sure why you the tblQtyOnHand is a breech of
normalization.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Ok that does seem pretty complicated! The problems I am having tho
are
due
to
current practices tho' (it is for a small not-for-profit housing
association). Stock is bought on a just in time basis by tradesmen
who
carry out the job and return excess stock to the (small) store.
each
tradesman receives a works order (from another system) that details
the
work
to be carried out, but the other system has no provision for keeping
track
of
what stock is where or on the financial info of stock bought. To
make
my
system as easy-to use as possible I wanted a form that collects
information
on products to be ordered for a particular job and prints out where
to
collect the stock (store or supplier). the print out is then to be
given
to
the tradesmen, who completes it with stock-used-where type
information
and
returns it on completion of the job. The user then goes back to the
system
and updates the "issue order" with the handwritten info. finally
the
system
needs to be accesed a third time by finance who will add the cost of
each
item after they finally receive the invoice (from supplier). So
what I
now
need to know is - if I proceed with designing the system this way,
how
would
I communicate with the tables you have shown me ie would shared info
be
accesed from one table to another?
Also - with regards to tblQtyOnHand - is this in breech of
normalization
rule - I get can get quite confused about normalizing.
Thank you again for taking the time over such a lengthy reply,
Cheers
Kenny
:
Ken,
The tables you need will depend a great deal on the level of
granularity
you
need for your business. The kind of inventory tracking system you
seem
to
want can range from fairly simple to extremely complicated. Let me
suggest
that you think of this in terms of "modules" that serve to provide
the
various pieces of your system. From what you have described so far,
it
appears to me that you need 3 "modules."
1. Ordering
2. Receiving
3. Inventory
If your looking for something simple, you might be able to include
the
Receiving "module" in the Inventory "module." There will be a great
deal
of
overlap in all these areas.
The Ordering module needs to track orders that have been placed to
suppliers, the quantities of those orders, the quantities that have
been
received against those orders (the quantity due against orders is
the
difference of those previous 2 figures), and, perhaps, the
quantities
that
have been billed. To determine the level of complexity this module
needs
to
have, you need to ask a couple of questions. (1) Will we ever place
orders
for items that we intend to have shipped to us at different times
or
that
we
need to have shipped to different locations in the company? If the
answer
to
that is yes, then you will need to add a shipment level to your
ordering
module -- say call it tblOrderShipments. (2) Will we ever need to
track
the
billing of orders to different departments within the company, or
will
we
always charge everything to the same "cost center"? If you need to
be
able
to charge out to multiple, then you will need a table to store
that --
let's
call it tblOrderDistributions. Here's how I would build the tables
if
it
went to that depth of complexity:
tblIssueOrder
IO_ID
IO_Date
--Other Fields related to an order header, if needed
tblIssueOrderDetails
IODetailID
IO_ID
ProductID
QtyRequired (If you go to the next level this would be a sum of
quantities
there and could be calculated)
-- Other fields related to order line detail
tblOrderShipments
OrderShipmentID
IODetailID
Quantity_Ordered
Quantity_Received
Quantity_Billed
-- Other fields related to shipments (such as department shipped
to)
tblOrderDistributions
OrderDistributionID
OrderShipmentID
CostCenter
QuantityCharged
--Other fields related to billing distributions
The Receiving module will track your receiving transactions. If you
are a
large company that has a receiving dock and items are held in that
dock
for
later movement into inventory, you might want to keep this separate
from
the
Inventory module. I would have these tables in it.
tblReceipts
ReceiptID
ReceiptDate
BillOfLading
ShippingCompany
-- Other fields related to a receipt header
tblReceiptDetails
ReceiptDetailID
ReceiptID
IO_ID (The next 4 fields can help you match receipts to orders --
you
will
need this to update the Quantity Received in your Order tables)
IODetailID
OrderShipmentID
OrderDistributionID
QuantityReceived
InventoryTargetLocation (a large warehouse may have several levels
of
locations -- Row, Rack, Bin, etc.)
--Other field related to receipt details
The Inventory module will track the on hand quantities of items in
your
warehouse/stock room and the movement of those items throughout the
company
until they are finally disposed of. If you are a large company
with a
Receiving Dock, then you will need to record the movement of items
from
Receiving into Inventory. If you are a small company you may be
able
to
do
that all in one step. You might have tables like this
tblInventoryTransactions
InventoryTransactionID
ProductId
Quantity
TransactionType (MovementIntoInventory or MovementOutOfInventory,
ReturnToSupplier,etc.)
InventoryLocationID (This will identify exactly where the item is
located)
tblQuantityOnHand
QuantityOnHandId
InventoryTransactionId
QuantityAvailable
Well, that was pretty complicated but, hopefully, it will help you
understand where you need to go next. Feel free to post back if you
have
other questions.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
I have been trying to design a db for simple stock controll for a
housing
assoc. I want users to be able to select repair materials from a
list
and
the quantities required for each repair. I then want users to
click a
button and the system to be able to print out where to collect
each
material
(ie from store or from supplier) the problem is I do not know
how
to
correctly model the materials? I have thought of tables such as:
([tbl IssueOrder]{IO_ID, IODate})
([tbleIssueOrderDetails]{IODetailID, IO_ID, ProductID,
QtyRequired})
which are linked as 1 tblIssueOrder has many tblIssueOrderDetails
and a products table as:
([tblProducts]{ProductID, ProducName})
but the problem is when the user clicks my check materials
button,
what
tables should I have? I was thinking of an aquisition table that
will
hold
details of materials as they come in and a 'goods out' to show
materials
used, but what about units on order, would it be appropeiate to
hold
a
third
set of tables for this or are there more conventional ways to
model
this
problem.
Any advice or comments would be much appreciated.
Thank you
