Aggregate Functions in Subqueries

[emorris1000]

Seemed appropriate to have a different thread for this issue, even though I have another thread for the original issue.

I have put together a nice little subquery that allows me to look at the flow of a gas over time and, by numerically differentiating time, allows me to calculate the amount of gas that has flowed over a period of time.

Here's the current query:

SELECT TestTable.PolyID,
 
 
([TestTable]![Time]-
Nz((SELECT TOP 1 Dupe.Time From TestTable AS Dupe 
WHERE dupe.Time < TestTable.Time 
ORDER BY Dupe.ID desc),0))
 
*[TestTable]![EthFlow] 

AS C2dT
 
FROM TestTable;

The query gives 2 fields, the PolyID (which are identical for all of these, they represent a single run), and the C2dT, which is the chunk where you subtract current time by previous time (the subquery) and multiply it by the EthFlow value.

From this I can create a second query where I sum the C2dT values, which is my ultimate goal. What I wanted to do was to just sum them in the same query, so I tried this:

SELECT TestTable.PolyID,
 
Sum(
([TestTable]![Time]-
Nz((select TOP 1 Dupe.Time
From TestTable AS Dupe 
WHERE dupe.Time < TestTable.Time 
Order by Dupe.ID desc),0))
 
*[TestTable]![EthFlow] 
)
AS C2dT
 
FROM TestTable
 
Group By PolyID;

But it won't let me do this because Time and Ethflow are not part of an aggregate function. I think I can get away with that if I mess with how I am grouping the subquery but I can't figure it out.

Any thoughts?

 

[Guus2005]

This is not a query the Access build-in query wizard can't handle.
Open the query in the query wizard and see what Access makes of it.

HTH

 

[jdraw]

You may have seen this already, but this is a well used reference for sub queries.
http://allenbrowne.com/subquery-01.html
http://allenbrowne.com/subquery-02.html

Hope it's useful.

 

[emorris1000]

Yeah that is a fantastic page, it's how I figured out how to get to where I am now.

Got a little closer, still can't figure it out though. If I ignore the polyID field I can get it to work:

SELECT 
Sum(
([TestTable]![Time]-
Nz((SELECT TOP 1 Dupe.Time From TestTable AS Dupe 
WHERE dupe.PolyID = TestTable.PolyID
AND dupe.Time < TestTable.Time 
ORDER BY Dupe.ID desc),0))*[TestTable]![EthFlow]) AS C2dT
FROM TestTable;

This works. It gives me the sum of the dT * EthFlow values like I wanted.

But if I try to add polyID (a foreign key that identifies a single run) it doesn't work anymore:

SELECT TestTable.PolyID,
Sum(
([TestTable]![Time]-
Nz((SELECT TOP 1 Dupe.Time From TestTable AS Dupe 
WHERE dupe.PolyID = TestTable.PolyID
AND dupe.Time < TestTable.Time 
ORDER BY Dupe.ID desc),0))*[TestTable]![EthFlow]) AS C2dT
FROM TestTable
Group By TestTable.PolyID;

It stops working, telling me that Time needs to be part of an aggregate function.

Edit:

SOLVED.

Ugly but I think I got it:

SELECT Q.PolyID, Sum(Q.C2dT) AS C2TotalFlow
FROM 
(SELECT TestTable.PolyID,  
([TestTable]![Time]-Nz((select TOP 1 Dupe.Time
From TestTable AS Dupe 
where dupe.Time < TestTable.Time 
Order by Dupe.ID desc),0))*[TestTable]![EthFlow] AS C2dT
FROM TestTable)  AS Q
GROUP BY Q.PolyID;

I just was messing up the subquery a bit, instead of putting the subquery as a SELECT (what I had done before) I put it as the FROM, which makes a lot more sense because I am nesting queries.