calculating quantities

[MikeO]

I apologize for re-posting but I am still having problems with calculating
certain values. The database stores units ordered, received, sold, shrinkage
in separate fields in Inventory Transactions table.
I can calculate usage, amount on hand, shrinkage etc. in a subform when I
call up each item. My problem is that I cannot get the Amount on hand to
display as a list of all items in a report or a form. I need to be able to
see what is on order at any time.
here is the expression from the subform. This displays the amount on hand
when I call up an item.
=Sum(nz([UnitsOrderedWarehouse])-nz([UnitsReceived]))
I have tried a query expression but get an aggregate error.
any help is greatly appreciated.
Mike O.

 

[KARL DEWEY]

The database stores units ordered, received, sold, shrinkage in separate
fields in Inventory Transactions table.
You should not use separate fields but like this --
TransDate - DateTime
Type - number (lookup) - 0-ordered, 1-received, 2-sold, 3-shrinkage,
4-inventory adjustment
QTY - number

To calculate on-hand - IIF([Type] = 1, [QTY], IIF([Type] = 2 Or [Type] = 3,
-[QTY], 0))

You can use all after last Type 4 TransDate to reset when inventory is
accomplished.

 

[MikeO]

Karl,
thank you for your reply but I am having trouble understanding the syntax.
Please explain your statement about "separate fields" . How can you record
received vs sold if they are not separate? or am I completely off track on
your explanation.
Thank you for your time.

fields in Inventory Transactions table.
You should not use separate fields but like this --
TransDate - DateTime
Type - number (lookup) - 0-ordered, 1-received, 2-sold, 3-shrinkage,
4-inventory adjustment
QTY - number

To calculate on-hand - IIF([Type] = 1, [QTY], IIF([Type] = 2 Or [Type] = 3,
-[QTY], 0))

You can use all after last Type 4 TransDate to reset when inventory is
accomplished.

I apologize for re-posting but I am still having problems with calculating
certain values. The database stores units ordered, received, sold, shrinkage
in separate fields in Inventory Transactions table.
I can calculate usage, amount on hand, shrinkage etc. in a subform when I
call up each item. My problem is that I cannot get the Amount on hand to
display as a list of all items in a report or a form. I need to be able to
see what is on order at any time.
here is the expression from the subform. This displays the amount on hand
when I call up an item.
=Sum(nz([UnitsOrderedWarehouse])-nz([UnitsReceived]))
I have tried a query expression but get an aggregate error.
any help is greatly appreciated.
Mike O.

 

[KARL DEWEY]

How can you record received vs sold if they are not separate?
Have field identifying the Type of transaction.

Karl,
thank you for your reply but I am having trouble understanding the syntax.
Please explain your statement about "separate fields" . How can you record
received vs sold if they are not separate? or am I completely off track on
your explanation.
Thank you for your time.

The database stores units ordered, received, sold, shrinkage in separate

fields in Inventory Transactions table.
You should not use separate fields but like this --
TransDate - DateTime
Type - number (lookup) - 0-ordered, 1-received, 2-sold, 3-shrinkage,
4-inventory adjustment
QTY - number

To calculate on-hand - IIF([Type] = 1, [QTY], IIF([Type] = 2 Or [Type] = 3,
-[QTY], 0))

You can use all after last Type 4 TransDate to reset when inventory is
accomplished.

I apologize for re-posting but I am still having problems with calculating
certain values. The database stores units ordered, received, sold, shrinkage
in separate fields in Inventory Transactions table.
I can calculate usage, amount on hand, shrinkage etc. in a subform when I
call up each item. My problem is that I cannot get the Amount on hand to
display as a list of all items in a report or a form. I need to be able to
see what is on order at any time.
here is the expression from the subform. This displays the amount on hand
when I call up an item.
=Sum(nz([UnitsOrderedWarehouse])-nz([UnitsReceived]))
I have tried a query expression but get an aggregate error.
any help is greatly appreciated.
Mike O.

 

[MikeO]

OK, got it!
That was the process I used in the past but I thought this may work better.
I will take another look at it and see what happens.
Thank you
Mike O

Have field identifying the Type of transaction.

Karl,
thank you for your reply but I am having trouble understanding the syntax.
Please explain your statement about "separate fields" . How can you record
received vs sold if they are not separate? or am I completely off track on
your explanation.
Thank you for your time.

The database stores units ordered, received, sold, shrinkage in separate
fields in Inventory Transactions table.
You should not use separate fields but like this --
TransDate - DateTime
Type - number (lookup) - 0-ordered, 1-received, 2-sold, 3-shrinkage,
4-inventory adjustment
QTY - number

To calculate on-hand - IIF([Type] = 1, [QTY], IIF([Type] = 2 Or [Type] = 3,
-[QTY], 0))

You can use all after last Type 4 TransDate to reset when inventory is
accomplished.

:

I apologize for re-posting but I am still having problems with calculating
certain values. The database stores units ordered, received, sold, shrinkage
in separate fields in Inventory Transactions table.
I can calculate usage, amount on hand, shrinkage etc. in a subform when I
call up each item. My problem is that I cannot get the Amount on hand to
display as a list of all items in a report or a form. I need to be able to
see what is on order at any time.
here is the expression from the subform. This displays the amount on hand
when I call up an item.
=Sum(nz([UnitsOrderedWarehouse])-nz([UnitsReceived]))
I have tried a query expression but get an aggregate error.
any help is greatly appreciated.
Mike O.

 

[MikeO]

Karl,
I am trying to use your suggestion for the quantity in one field [QTY] with
transaction codes of "I" for issue, "R" for received "O" for order, etc.
I am unclear on the syntax for a report that is controlled by a query. The
result I need is qunatity on hand, quanitity on order.

I appreciate you input on this matter.
Mike

Have field identifying the Type of transaction.

Karl,
thank you for your reply but I am having trouble understanding the syntax.
Please explain your statement about "separate fields" . How can you record
received vs sold if they are not separate? or am I completely off track on
your explanation.
Thank you for your time.

The database stores units ordered, received, sold, shrinkage in separate
fields in Inventory Transactions table.
You should not use separate fields but like this --
TransDate - DateTime
Type - number (lookup) - 0-ordered, 1-received, 2-sold, 3-shrinkage,
4-inventory adjustment
QTY - number

To calculate on-hand - IIF([Type] = 1, [QTY], IIF([Type] = 2 Or [Type] = 3,
-[QTY], 0))

You can use all after last Type 4 TransDate to reset when inventory is
accomplished.

:

I apologize for re-posting but I am still having problems with calculating
certain values. The database stores units ordered, received, sold, shrinkage
in separate fields in Inventory Transactions table.
I can calculate usage, amount on hand, shrinkage etc. in a subform when I
call up each item. My problem is that I cannot get the Amount on hand to
display as a list of all items in a report or a form. I need to be able to
see what is on order at any time.
here is the expression from the subform. This displays the amount on hand
when I call up an item.
=Sum(nz([UnitsOrderedWarehouse])-nz([UnitsReceived]))
I have tried a query expression but get an aggregate error.
any help is greatly appreciated.
Mike O.