Subquery in an aggregate query.

[Cindy]

I'm trying to get the average minutes for each unique lane per customer. I'm
using a totals query (obviously not correctly) but having issues.

I want to subtract the Arrival time from the Departure time, then subtract
720 minutes from any that are over 720 minutes (to account for overnight,
non-driving time) and THEN average what is left. I have broken it down into
three calculations because it didn't like it any other way but I am stuck on
the last one. The AvgEditedTime gives me the error that it can't do a
subquery on the aggregate of the Edited Time IIf statement. For the RunTime
I am putting Sum in the Totals row and in EditedTime I have Expression, I
have tried Expression, Where, Sum and Avg in the AvgEditedTime with no luck.

I'm not sure I'm even on the right track here but I'm putting my SQL here in
hopes someone can point me in the right direction.

SELECT [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name], Sum(DateDiff("n",[Departure],[Arrival])) AS RunTime,
IIf([RunTime]>720,[RunTime]-720,[RunTime]) AS EditedTime, Avg([EditedTime])
AS AvgEditedTime, [Prof Rev3].[T2Loaded]
FROM [Prof Rev3]
GROUP BY [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name], [Prof Rev3].[T2Loaded]
HAVING ((([Prof Rev3].[T2Loaded])="L"))
ORDER BY [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name];

Thanks in advance!!!
Cindy

 

[KARL DEWEY]

The problem is trying to use an alias in the same query that produced it.
Access maight just try processing the formula that has the alias before it is
created.
Two solutions - Use the same calculations that produced the alias in the
second formula instead of the alias or use separate queries.

 

[Hans Up]

SELECT [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name], Sum(DateDiff("n",[Departure],[Arrival])) AS RunTime,
IIf([RunTime]>720,[RunTime]-720,[RunTime]) AS EditedTime, Avg([EditedTime])
AS AvgEditedTime, [Prof Rev3].[T2Loaded]
FROM [Prof Rev3]
GROUP BY [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name], [Prof Rev3].[T2Loaded]
HAVING ((([Prof Rev3].[T2Loaded])="L"))
ORDER BY [Prof Rev3].[T0Customer_Id], [Prof Rev3].[T3City_Name], [Prof
Rev3].[T4City_Name];

I will second Karl's suggestion to break it into separate queries.
Maybe start with this one, and save it as qryRunTimes.

SELECT
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name,
Sum(DateDiff("n",Departure,Arrival)) AS RunTime
FROM Prof_Rev3 AS p
GROUP BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name
WHERE
p.T2Loaded="L"
ORDER BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name;

Notice I used a short alias in place of the table name when referencing
fields and I discarded all the square brackets. The only brackets which
were actually required were those surrounding the table name. But I was
able to eliminate those by renaming the table to Prof_Rev3 (because I
dislike spaces in table names).

Also your original HAVING clause indicates you're only interested in
rows where T2Loaded = "L". So I converted the HAVING to a WHERE clause,
which meant I could also drop T2Loaded from the GROUP BY expression.

If qryRunTimes works correctly, you can use it as the source for another
query where you derive EditedTime. So try this as qryEditedTimes.

SELECT
r.T0Customer_Id,
r.T3City_Name,
r.T4City_Name,
r.RunTime,
IIf(RunTime>720,RunTime-720,RunTime) AS EditedTime
FROM qryRunTimes AS r;

And finally if qryEditedTimes works, you should be able to derive your
AvgEditedTime with it.

Good luck,
Hans

 

[Hans Up]

SELECT
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name,
Sum(DateDiff("n",Departure,Arrival)) AS RunTime
FROM Prof_Rev3 AS p
GROUP BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name
WHERE
p.T2Loaded="L"
ORDER BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name;

That was wrong. The WHERE should be before the GROUP BY.

SELECT
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name,
Sum(DateDiff("n",Departure,Arrival)) AS RunTime
FROM Prof_Rev3 AS p
WHERE
p.T2Loaded="L"
GROUP BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name
ORDER BY
p.T0Customer_Id,
p.T3City_Name,
p.T4City_Name;