Inventory movement

[vbnetman via AccessMonster.com]

I'm setting up an inventory movement table to track parts. Within the table
are fields for (among others) part number, transaction type and quantity. I
need to show a quantity on hand. Can I have a QOH field based on quantities
added or subtracted in the quantity field in this table or is this poor
design?

Thank you kindly

vb

 

[Ken Snell MVP]

For most databases, you can just calculate the quantity on hand using a
textbox on a form or report, where the textbox's ControlSource is a DSum
expression to add the quantities values and give you the result.

Very, very, very large databases may take a long time to calculate the
quantity on hand, so in those cases, an interim table often is used to hold
calculated values ("roll up" values) for quicker display on forms and
reports.

 

[vbnetman via AccessMonster.com]

Hi Ken and thank you for the response. In my application I am using a listbox
to display information relative to a part, spreadsheet style ie; part no,
description etc. It is in this 'spreadsheet' that I would like to include
the QOH. Without having this QOH field actually in a table, I'm not sure that
I can display the information as I would like. In my case, I do not think
that time is a critical factor. The mechanics of including this QOH field
seems non-normalised and would this result in major issues down the road?

Thank you

vb

For most databases, you can just calculate the quantity on hand using a
textbox on a form or report, where the textbox's ControlSource is a DSum
expression to add the quantities values and give you the result.

Very, very, very large databases may take a long time to calculate the
quantity on hand, so in those cases, an interim table often is used to hold
calculated values ("roll up" values) for quicker display on forms and
reports.

I'm setting up an inventory movement table to track parts. Within the
table

[quoted text clipped - 8 lines]

vb

 

[Steve]

Hello VB,

Your tables should basically look like:
TblPart
PartID
PartNumber
Quantity

TblTransactionType
TransactionTypeID
TransactionType

TblTransaction
TransactionID
TransactionDate
PartID
TransactionTypeID
TransactionQuantity

You then need to design a form to record a transaction and saves the data to
TblTransaction. In that same form, when a transaction is recorded Quantity
in TblPart should be automatically adjusted by adding or subtracting
TransactionQuantity depending on TransactionTypeID.

Steve
(e-mail address removed)

 

[Ken Snell MVP]

The query that provides the data to the listbox could include a calculated
field to give you the quantity on hand; for example:

SELECT PartNumber, PartDescrption,
(SELECT Sum(T.Quantity) AS SQ
FROM PartsTable AS T
WHERE T.PartNumber = PartsTable.PartNumber)
AS TotalQuantityOnHand
FROM PartsTable;


And yes, including such a summed field in a table can lead to
synchronization problems, moreso than denormalization problems.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken and thank you for the response. In my application I am using a
listbox
to display information relative to a part, spreadsheet style ie; part no,
description etc. It is in this 'spreadsheet' that I would like to include
the QOH. Without having this QOH field actually in a table, I'm not sure
that
I can display the information as I would like. In my case, I do not think
that time is a critical factor. The mechanics of including this QOH field
seems non-normalised and would this result in major issues down the road?

Thank you

vb

For most databases, you can just calculate the quantity on hand using a
textbox on a form or report, where the textbox's ControlSource is a DSum
expression to add the quantities values and give you the result.

Very, very, very large databases may take a long time to calculate the
quantity on hand, so in those cases, an interim table often is used to
hold
calculated values ("roll up" values) for quicker display on forms and
reports.

I'm setting up an inventory movement table to track parts. Within the
table

[quoted text clipped - 8 lines]

vb

 

[vbnetman via AccessMonster.com]

Hi Steve,
You've answered my question. Thank you so much!

vb

Hello VB,

Your tables should basically look like:
TblPart
PartID
PartNumber
Quantity

TblTransactionType
TransactionTypeID
TransactionType

TblTransaction
TransactionID
TransactionDate
PartID
TransactionTypeID
TransactionQuantity

You then need to design a form to record a transaction and saves the data to
TblTransaction. In that same form, when a transaction is recorded Quantity
in TblPart should be automatically adjusted by adding or subtracting
TransactionQuantity depending on TransactionTypeID.

Steve
(e-mail address removed)

I'm setting up an inventory movement table to track parts. Within the
table

[quoted text clipped - 8 lines]

vb

 

[SF]

The query that provides the data to the listbox could include a calculated
field to give you the quantity on hand; for example:

SELECT PartNumber, PartDescrption,
(SELECT Sum(T.Quantity) AS SQ
FROM PartsTable AS T
WHERE T.PartNumber = PartsTable.PartNumber)
AS TotalQuantityOnHand
FROM PartsTable;


And yes, including such a summed field in a table can lead to
synchronization problems, moreso than denormalization problems.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken and thank you for the response. In my application I am using a
listbox
to display information relative to a part, spreadsheet style ie; part no,
description etc. It is in this 'spreadsheet' that I would like to
include
the QOH. Without having this QOH field actually in a table, I'm not sure
that
I can display the information as I would like. In my case, I do not think
that time is a critical factor. The mechanics of including this QOH field
seems non-normalised and would this result in major issues down the road?

Thank you

vb

For most databases, you can just calculate the quantity on hand using a
textbox on a form or report, where the textbox's ControlSource is a DSum
expression to add the quantities values and give you the result.

Very, very, very large databases may take a long time to calculate the
quantity on hand, so in those cases, an interim table often is used to
hold
calculated values ("roll up" values) for quicker display on forms and
reports.

I'm setting up an inventory movement table to track parts. Within the
table
[quoted text clipped - 8 lines]

vb

 

[Fred]

There are basically two underlying structural approaches towards inventory.

One is simply a list of "transactions" (including additions, withdrawals.
the initial entry, adjustments to reconcile to physical inventories). The
QOH is basically a calculation that is made when needed. This is the method
in Allen Brown's example, and the basis of Ken Snell's reply.

The other is QOH is a value stored in a table. And to have it so that each
transaction modifies the QOH. This is the basis of Steve's reply, (although
I've always considered the approach of just putting out proposed table
designs without much explanation or addressing of the issues to be a weaker
answer.) This method is more complex to set up and complex to successfully
administer (you have to make sure that every transaction modifies the QOH
value, and makes sure that this modification happens once and only once for
each transaction.