Y
Yam84
Hello,
I am having a pretty common issue from what I have read, although i
have been unable to remedy my problem.
I have a query, that I would like to union with another query however
I am receiving an error. here is my qry:
SELECT [Check InOut].empID, [Check InOut].productID, Sum([Check
InOut].checkOutQuantity) AS qtycheckedout, [Check
InOut].purchasetypeID
FROM PurchaseType INNER JOIN [Check InOut] ON
PurchaseType.purchasetypeID = [Check InOut].purchasetypeID
GROUP BY [Check InOut].empID, [Check InOut].productID, [Check
InOut].purchasetypeID;
I would like to add this Expression to the qry:
IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]!
[qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1
However, when I try to implement this into my qry, i get the aggregate
function error on the expression.
the purpose of this expression is to make all the purchasetypes 1-3
quantities negative and the purchasetype 4, positive, so that i may
use these numbers to assist me in calculating the amount on hand.
CheckIn/Out--stores information about check in/outs, and purchases
(Transaction Table)
checkinoutID
checkoutdt
checkindt
empid
prodid
checkqty
checkindtacc (actual check in dt, which is the field that i want to
put the after event update, meaning a person has returned a checked
out item, so the qty they checked out should add back into stock)
purchasetype tbl:
purchase typeID
purchase type (purchase or credit (1,2), could add check in (4) and
check out (3))
Is there anyway to include this If statement in my query so that it
will return to me the results I need?
I am having a pretty common issue from what I have read, although i
have been unable to remedy my problem.
I have a query, that I would like to union with another query however
I am receiving an error. here is my qry:
SELECT [Check InOut].empID, [Check InOut].productID, Sum([Check
InOut].checkOutQuantity) AS qtycheckedout, [Check
InOut].purchasetypeID
FROM PurchaseType INNER JOIN [Check InOut] ON
PurchaseType.purchasetypeID = [Check InOut].purchasetypeID
GROUP BY [Check InOut].empID, [Check InOut].productID, [Check
InOut].purchasetypeID;
I would like to add this Expression to the qry:
IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]!
[qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])) AS Expr1
However, when I try to implement this into my qry, i get the aggregate
function error on the expression.
the purpose of this expression is to make all the purchasetypes 1-3
quantities negative and the purchasetype 4, positive, so that i may
use these numbers to assist me in calculating the amount on hand.
CheckIn/Out--stores information about check in/outs, and purchases
(Transaction Table)
checkinoutID
checkoutdt
checkindt
empid
prodid
checkqty
checkindtacc (actual check in dt, which is the field that i want to
put the after event update, meaning a person has returned a checked
out item, so the qty they checked out should add back into stock)
purchasetype tbl:
purchase typeID
purchase type (purchase or credit (1,2), could add check in (4) and
check out (3))
Is there anyway to include this If statement in my query so that it
will return to me the results I need?