calculating quantities

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

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

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.

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

KARL DEWEY

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

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

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

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

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.
 

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

Top