calculating quantities

  • Thread starter Thread starter MikeO
  • Start date Start date
M

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

KARL DEWEY said:
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 said:
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.
Click to expand...
Click to expand...
 
How can you record received vs sold if they are not separate?
Have field identifying the Type of transaction.

MikeO said:
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.

KARL DEWEY said:
The database stores units ordered, received, sold, shrinkage in separate
Click to expand...
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
Have field identifying the Type of transaction.

MikeO said:
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.

KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
Have field identifying the Type of transaction.

MikeO said:
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.

KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top