Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)
From: Stephen Rasey (raseysm_at_wiserways.com)
Date: 08/25/04
- Next message: David: "specifying Date criteria"
- Previous message: John Spencer (MVP): "Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)"
- In reply to: John Spencer (MVP): "Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Aug 2004 18:39:51 -0500
No, I'm not stuck. I'm done.
Option 2: create another table to hold the items that were in the IN clause
was the way to go.
It is table H82 in the following disguised query tree.
With 5 elements in the H82 table (returning 5 columns in the query as part
of the T4.* in T1J6), the query executes in 1 second, 1000 records returned,
which I think is splended. Add 0.25 seconds for a dao delete and write for
up to 12 elements in H82, and we have a very acceptable application
response.
T1J4qnuAggSort:
TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score
SELECT T2.A2IDSet, T2.OO, T2.PP
FROM ((H80 AS H80 INNER JOIN PP ON H80.IDH=H71.IDH) INNER JOIN J1T2qmax AS
T2 ON H71.OO=T2.OO) INNER JOIN H82 ON H80.IDTraceH=H82.IDTraceH
GROUP BY T2.A2IDSet, T2.OO, T2.PP
ORDER BY T2.OO
PIVOT H80.IDTraceH;
T1J5qxt:
SELECT T2.A2IDSet, T2.OO, T2.PP, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.CCT) AS AvgOfCCT, Min(H71.CC) AS MinOfCC, Min(H71.CCT) AS
MinOfCCT
FROM H82 INNER JOIN ((H80 INNER JOIN H71 ON H80.IDH = H71.IDH) INNER JOIN T2
ON H71.OO = T2.OO) ON H82.IDTraceH = H80.IDTraceH
GROUP BY T2.A2IDSet, T2.OO, T2.PP
ORDER BY Avg(H71.YY), Avg(H71.CC);
T1J6qnuAggSortedCrossTab:
SELECT T5.OO, T5.PP, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*
FROM J1T5qnuAggSort AS T5 INNER JOIN J1T4qxt AS T4 ON T5.OO = T4.OO
ORDER BY T5.AvgOfYY, T5.AvgOfCC;
Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org
"John Spencer (MVP)" <spencer4@comcast.net> wrote in message
news:412D1A6E.3583868A@comcast.net...
> Yes, I can see that you would have to use an IN clause in the Crosstab
query and
> specify the column names and since you cannot do that according to you
earlier
> messages then I think you are stuck unless you use some VBA to get the
column
> names from the just the crosstab and then build your overall query with
vba.
>
> :-(
- Next message: David: "specifying Date criteria"
- Previous message: John Spencer (MVP): "Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)"
- In reply to: John Spencer (MVP): "Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
