Aggregate query - calculations (1 Viewer)

mjdemaris · Dec 21, 2015

Problem:
I want to view all records with an actual quantity below a set level.

T-Parts - Item description, Stock level
T-Transactions - date of all transactions
T-TransactionDetails - Master Number(FK), transtype, quantity
T-TransTypes - type, result (positive or negative 1 depending on addition or deletion to stock levels)

The query(ies) need to find all parts with a current stock level > 0. Done.
Then, calculate the actual quantity for each record, be it 100 or 3000.
When I try to include an expression: Quantity * Result on the same query that selects the parts, I get errors about displaying a certain field or that the query does not include the specified expression Quantity * Result as part of an aggregate function.

The SQL that provides this error is:

Code:

PARAMETERS prmDate DateTime;
SELECT [Quantity]*[Result] AS ActualStock, [T-TransactionDetails].MasterID_FK
FROM [T-TransTypes] INNER JOIN ([T-Transactions] INNER JOIN [T-TransactionDetails] ON [T-Transactions].TransID = [T-TransactionDetails].TransID_FK) ON [T-TransTypes].TransTypeID = [T-TransactionDetails].TransTypeID_FK
WHERE ((([T-Transactions].TransDate)>=[prmDate]))
GROUP BY [T-TransactionDetails].MasterID_FK;

The SQL that selects the parts:

Code:

SELECT [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].WinterLevel
FROM [T-Parts]
WHERE ((([T-Parts].WinterLevel)>0))
ORDER BY [T-Parts].MasterNum;

Attached is a picture of the relationships showing the parts table also.

llkhoutx · Dec 21, 2015

You've probably got a Null used in the multiplication. Use a conditional If with an IsError, e.g.

IIf(iserror(a*b,0,a*b)

mjdemaris · Dec 21, 2015

Well, it worked with no errors! Thanks, Houston!

Except when I try to display the actual quantity. I get five records, because only five have transaction data associated with them.

But when attempting to display the actual quantity expression, I get the error I posted previously.

mjdemaris · Dec 21, 2015

I am attempting to use the output for a report, if that makes any difference.

mjdemaris · Dec 21, 2015

OK.
I have to admit operator error! I forgot to include SUM before the expression!

ActualQuantity: SUM(IIF(IsError([Quantity]*[Result]),0,[Quantity]*[Result]))

This works!

Thanks again, Houston!

Well, almost solved. The other part of the problem is finding those records with a certain transaction type and using the date of that transaction (stock take) as the starting point for the calculation.

I already have a query to do this for one item at a time, using VBA Record sets. Now I just need find a way to integrate it into multiple records at once.

The SQL for the LastStockTake query is:

Code:

 PARAMETERS prmMaster Short;
SELECT [T-Transactions].TransDate AS LastStockTake, [T-TransactionDetails].Quantity
FROM [T-Transactions] INNER JOIN [T-TransactionDetails] ON [T-Transactions].TransID = [T-TransactionDetails].TransID_FK
GROUP BY [T-Transactions].TransDate, [T-TransactionDetails].Quantity, [T-TransactionDetails].MasterID_FK, [T-TransactionDetails].TransTypeID_FK
HAVING ((([T-TransactionDetails].MasterID_FK)=[prmMaster]) AND (([T-TransactionDetails].TransTypeID_FK)=5))
ORDER BY [T-Transactions].TransDate DESC;

I think that if I create a query (subquery) using this code, without the parameter, it may work.

And I got it to work, using a subquery, and using the resulting date in the WHERE clause.

Thanks for your help!

Aggregate query - calculations (1 Viewer)

mjdemaris

Working on it...

Attachments

llkhoutx

Registered User.

mjdemaris

Working on it...

mjdemaris

Working on it...

mjdemaris

Working on it...

Similar threads

Users who are viewing this thread