Table Design Problem

G

Guest

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
 
L

Lynn Trapp

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



Ken said:
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
 
G

Guest

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


Lynn Trapp said:
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



Ken said:
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
 
L

Lynn Trapp

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



Ken said:
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


Lynn Trapp said:
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



Ken said:
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
 
G

Guest

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

Lynn Trapp said:
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



Ken said:
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


Lynn Trapp said:
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
 
L

Lynn Trapp

In this case, you would need to update the quantity value in tblQtyOnHand to
store the CURRENT quantity available. This will need to be increased each
time an item is received and decremented each time it is delivered.

--
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



Ken said:
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

Lynn Trapp said:
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



Ken said:
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
 
G

Guest

sorry, I didnt mean to confuse the issue with ref to the other system, but I
dont get the link between tblQtyOnhand and the tblInventoryTransactions

Lynn Trapp said:
In this case, you would need to update the quantity value in tblQtyOnHand to
store the CURRENT quantity available. This will need to be increased each
time an item is received and decremented each time it is delivered.

--
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



Ken said:
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

Lynn Trapp said:
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
 
L

Lynn Trapp

ah, I get what you are referring to now. I wrote all that on the fly and
the relationship between those tables probably needs to go the other
direction, with tblQtyOnHand being the parent.

--
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



Ken said:
sorry, I didnt mean to confuse the issue with ref to the other system, but
I
dont get the link between tblQtyOnhand and the tblInventoryTransactions

Lynn Trapp said:
In this case, you would need to update the quantity value in tblQtyOnHand
to
store the CURRENT quantity available. This will need to be increased each
time an item is received and decremented each time it is delivered.

--
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



Ken said:
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
 
G

Guest

Hi, My comp crashed yesterday, but I would like to say a big thank you for
helping me get my head around a few 'issues' with this system.
Thank You,

Kenny
 
G

Guest

Ok you said feel free to post back.. so here goes:

I have the following tables now set up:

tblPO --> tblPOdetail
tblIO --> tblIODetail
tblJobReuirement -->tblJobReuirementDetail

(-->shows relationship)

Each 'detail' is linked to tblProducts.


Users can open a job requirement, enter some products required quantities
and some code then creates POs' and IssueOrders(IOs') as required. (based on
quantities of stock which is calculated by a function).
Users can also create PurchaseOrders (POs') on their own.

The problem is I am unsure how to represent cost information.
Each IO has an associated Works Order Ref (Generated by another system and
entered by the user). Each WO Ref is associated to a property ref (again
property ref is on other db)
What I would like to do is provide an option that allows the user to select a
property ref and the db to return the amount spent on that property.

I thought I could either put unit price info in the products table (but prices
change often meaning new products would need to be added each time
the price changed) or put unit info in the POdetail lines, but this would
mean I
couldnt query based on WOref as each PO (and indeed each PO line) could have
many WOrefs


hopefully you are able to give some guidance,
Thank you in advance,
Kenny
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top