Aggregate function error

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?
 
J

Jerry Whittle

Try making a simple query with the IIF statement and get it to work. Then use
is as the record source for the Totals query. Once that works to your
satisfaction, use the second query as part of the union query.
 
M

Michel Walsh

I *assume* that given the actual group by clause, there is only one
possible value for qtyCheckedOut. If so, try:



IIf(PurchaseType.purchasetypeID<=3,
LAST(CheckedOutQry.qtycheckedOut)*-1,
LAST(CheckedOutQry.qtycheckedOut)) AS Expr1



or


LAST( IIf(PurchaseTypepurchasetypeID<=3,
CheckedOutQry.qtycheckedOut*-1,
CheckedOutQry.qtycheckedOut))AS Expr1



Hoping it may help,
Vanderghast, Access MVP
 

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