Inventory movement

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

--
Message posted via http://www.accessmonster.com

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.

--

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


"vbnetman via AccessMonster.com" u22585@uwe wrote in message
news:9cbff2ca15c3d@uwe...
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

--
Message posted via http://www.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


Ken Snell MVP wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200909/1

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



"vbnetman via AccessMonster.com" u22585@uwe wrote in message
news:9cbff2ca15c3d@uwe...
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

--
Message posted via http://www.accessmonster.com

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/


"vbnetman via AccessMonster.com" u22585@uwe wrote in message
news:9cc1865857975@uwe...
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


Ken Snell MVP wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200909/1

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

vb

Steve wrote:
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


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

vb

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200909/1

"Steve" schreef in bericht
m...
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



--
Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... very soon we will 'celebrate' 10.000
pageloads...)
== I am afraid that TODAY we 'need to' celebrate 10.000 pageloads...

For those who don't 'agree' with this mail , because $teve was 'helpfull' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'StopThisAdvertising' and you won't see these mails....

Arno R

"Ken Snell MVP" wrote in message
...
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/


"vbnetman via AccessMonster.com" u22585@uwe wrote in message
news:9cc1865857975@uwe...
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


Ken Snell MVP wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200909/1

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.