Expressions in queries

[Yam84]

Hello:

I have created a query which tracks items that are checked out and purchased from stock. I have a purchasetype tbl which identifies they type of transactions. 1-3 deduct from stock, while 4 (indicates an item that was checked out is checked back in) adds to stock.
I have created a query which sums the stock quantities (AcqDetail Qry).

SELECT Sum(StockTake.stockQuantity) AS SumOfstockQuantity, StockTake.stockTakeDt, StockTake.productID
FROM Acq INNER JOIN (AcqDetail INNER JOIN StockTake ON (AcqDetail.acqDetailID = StockTake.acqDetailID) AND (AcqDetail.acqDetailID = StockTake.acqDetailID) AND (AcqDetail.productID = StockTake.productID)) ON Acq.AcqID = AcqDetail.acqID
GROUP BY StockTake.stockTakeDt, StockTake.productID
ORDER BY StockTake.productID;


I have another query that sums quantity taken out of stock by the transaction (that deduct from stock) by item (CheckedOutQry).

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
HAVING ((([Check InOut].purchasetypeID) Not In (4)));


I would like to add an expression to this query to make any purchasetypes 1-3 a negative number, otherwise assign that as a positive number.
Expr1: IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut]))

I have been unable to accomplish this as I am always being prompted to enter the [CheckedOutQry]![qtycheckedOut], which in my mind is already being passed to this qry by the checkedoutqry or I have received a message telling me I have tried to execute a query that does not include the specified expression 'IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut]))' as a part of an aggregate function.

My overall goal is to use these two queries to create a UNION so that I may calculate the Amount On hand of an item.

I appreciate any light you can shed on this.

 

[jal]

As for dealing with negative numbers, I would start with two separate queries. Do a query that pulls all the positive values (do not sum them).

Then do a query that pulls all the negative values.

Then union the two queries as to form one large result set.

Then have an outer query that does a sum on that large result set. The sum will therefore take into account both the positive values and negative values as to yield the correct sum, I should think.

 

[Yam84]

I understand your suggestion, however it will only work until the checkout record is edited to reflect the check in. there is only one record, not two. If there were a record being created for checkins, i believe your suggestion would be possible. Maybe I should create it so that there is a new record for check ins, otherwise there would never be data in the query that calculates the checkouts because they would eventually turn to check ins. Maybe a visual would be better for you:

Product StockQuantity transtype checkoutqty AOH
Hammers 7 3 (-)2 5--------àInitially this is correct, but when it is checked in it should be checkoutqty + AOH. The way it is now, this is not how checkins are calculated. It just shows as a positive number so when edited, the number increases the AOH to 9
((Hammers 7 4 (+)2 9--------àThis is how the record look after it has been edited and AOH inaccurately increases to 9 as opposed to the accurate amount which is 7))
Nails 10 2 (-)5 5
Hammers 7 1 (-)2 3
Or should I somehow create a new record for checkins to take into acct that stock being added back in, then at the end, do a summation on the amts checked out and in and deduct and/or add that number to the stock qty to get the actual amount?
Hammers 7 4 (+)2 7

I have not been able to get the IIF function that accomplishes making the numbers negative based on the purchase type. ((Negate Decreasing stock)
Expr1: IIf([PurchaseType].[purchasetypeID]<=3,[CheckedOutQry]![qtycheckedOut]*-1,([CheckedOutQry]![qtycheckedOut])))

I was once able to get the UNION working, but to me it just looked like a combination of both queries, and I was unable to use the information to calculate the AOH.

Any suggestion for getting my IIf working?

 

[jal]

As for getting the IIF working, I don't know how to use the query grid, but if using the sql view, you can probably replace the Sum clause with the IIF clause, and then surround the query with an outer query that does the Sum. Maybe I'll try this myself.

 

[jal]

On thing that confused me - why do you group by EmpID?

If you just need to know stock quantities, who cares which emp sold the item?

 

[jal]

I set up a simplified version of your tables (e.g. I removed the EmpID column) and gave it a try. Maybe this will help you.

Select ProductID, Sum(AmtCheckedOut) as AmtChecked FROM
(

SELECT [Check InOut].productID, IIf([PurchaseType].[purchasetypeID]<=3,[Check InOut].checkoutQty*-1,([Check InOut].checkoutQty)) as AmtCheckedOut
FROM PurchaseType INNER JOIN [Check InOut] ON PurchaseType.purchasetypeID = [Check InOut].purchasetypeID
)

GROUP BY [Check InOut].productID

(See attached tables).

 

[Yam84]

Thanx Jal.

I see how you got yours to work and tried to model mine after it, however, did not get the same result. I noticed that in your query 1, after it is ran, it prompts for a quantitycheckedout, i click enter to bypass this and it returns to me a table w/ two negative values for two items, but the rest of the items are blank (I expect the purchasetypes 1-3 to return neg, but a 4 should return a positive number as opposed to a blank field). When I create my query 1, it prompts me twice for a quantitycheckedout, and returns nothing to me but a list of items and blank quantitycheckedout fields for each item. The IF statement seems to be doing nothing to the result set. I noticed that in your relationship, there is an undetermined relationship between your check inout and purchase type table, mine is a 1-to-many. should I take out my relationship? I have attached it for your review.