Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Stephen Rasey (raseysm_at_wiserways.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 14:08:23 -0500

the table B reference is in
INNER JOIN B On....

That first query was a simple one for illistration to highlight the FROM
(TRANSFORM..
. Next time, I'll use tblA, tblB.

Toward the bottom I have the (disguised) real query
SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*
 FROM
    (SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC)
AS
 AvgOfCC, Avg(H71.TT) AS AvgOfTT
     FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)
     INNER JOIN T2 ON H71.OO = T2.OO
     WHERE (((H80.IDTraceH) In (1410,1488,1520)))
     GROUP BY T2.A2IDSet, T2.OO, T2.DD
     ORDER BY Avg(H71.YY), Avg(H71.CC))
 AS T5
 INNER JOIN
     ( TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score
        SELECT T2.A2IDSet, T2.OO, T2.DD
         FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)
                 INNER JOIN T2 ON H71.OO = T2.OO
         WHERE (((H80.IDTraceH) In (1410,1488,1520)))
         GROUP BY T2.A2IDSet, T2.OO, T2.DD
         ORDER BY T2.OO
         PIVOT H80.IDTraceH)

 AS T4 ON T5.OO = T4.OO
 ORDER BY T5.AvgOfYY, T5.AvgOfCC;

Stephen Rasey
Houston

"Lynn Trapp" <ltrappNoSpam@ltcomputerdesigns.com> wrote in message
news:unhfYsgiEHA.1376@TK2MSFTNGP11.phx.gbl...
> So what is "B"? You use it in your select statement AND in your join
> statement, but it is never identified anywhere in a from clause. Is "B"
the
> name of the table or is it intended to be an alias to some other table
name?
>
> --
> Lynn Trapp
> MS Access MVP
> www.ltcomputerdesigns.com
> Access Security: www.ltcomputerdesigns.com/Security.htm
>
>
> "Stephen Rasey" <raseysm@wiserways.com> wrote in message
> news:OjS28dgiEHA.3664@TK2MSFTNGP11.phx.gbl...
> > Summary Question:
> >
> > Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL
> > statement where one of the inner queryies is a crosstab TRANSFORM query
as
> > in:
> > SELECT A.*,B.*
> > FROM
> > (TRANSFORM Score
> > SELECT t2.C, t2.D FROM T2
> > GROUP By T2.C, T2D
> > ORDER BY T2.C
> > PIVOT T2.E) As A
> > INNER JOIN B ON A.C = B.C;
> >
> > I get a syntax error. I suspect that a TRANSFORM cannot be an inline
> > subquery, but maybe there is a simple syntax error.
> >
> > Stephen Rasey
> > Houston
> > http://wiserways.com
> > http://excelsig.org
> >
> > Question in Detail:
> >
> > This is a cross tab query that works.
> > Call it J1T4qxt.
> > TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score
> > SELECT T2.A2IDSet, T2.OO, T2.DD
> > FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)
> > INNER JOIN T2 ON H71.OO = T2.OO
> > WHERE (((H80.IDTraceH) In (1410,1488,1520)))
> > GROUP BY T2.A2IDSet, T2.OO, T2.DD
> > ORDER BY T2.OO
> > PIVOT H80.IDTraceH;
> >
> > Note the WHERE ... IN clause. I need the PK's in the cross tab query
to
> > have high speed.
> >
> > I need to sort the results based upon an Aggregate. This, too, runs
> > quickly.
> > A subquery for the sort is J1T5qnu
> >
> > SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
> > AvgOfCC, Avg(H71.TT) AS AvgOfTT
> > FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)
> > INNER JOIN T2 ON H71.OO = T2.OO
> > WHERE (((H80.IDTraceH) In (1410,1488,1520)))
> > GROUP BY T2.A2IDSet, T2.OO, T2.DD
> > ORDER BY Avg(H71.YY), Avg(H71.CC);
> >
> > This WHERE has the same IN Clause as J1T4.
> >
> > I join J1T4 and J1T5 for the final result. It is a fast query for what
> it
> > does. About 3 sec.
> >
> > J1T6:
> > SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*
> > FROM J1T5qnu AS T5 INNER JOIN J1T4qxt AS T4 ON T5.OO = T4.OO
> > ORDER BY T5.AvgOfYY, T5.AvgOfCC;
> >
> > The problem is that J1T5 and J1T6 both have an ' In (1410,1488,1520) '
> > clause that is necessary for speed of execution.
> >
> > To make this useful, the parent application must change the IN clause
> > dynamically.
> >
> > My options are:
> >
> > 1. use Parameter queries for J1T5 and J1T4. I've never tried
> Parameters
> > for an entire IN clause so I don't know if that will work at all. I
> guess
> > this is another question to post.
> >
> > 2. Create another table H81 that has only the IDTraceH values to use
and
> > rewrite J1T5 and J1T6 to JOIN on H81.
> >
> > 3. Build J1T4 and J1T5 in QueryDef and Execute J1T6. I think option
2
> > is superior to this.
> >
> > 4. Create a compound SQL string programatically that will look
something
> > like this:
> >
> > SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*
> > FROM
> > (SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC)
> AS
> > AvgOfCC, Avg(H71.TT) AS AvgOfTT
> > FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)
> > INNER JOIN T2 ON H71.OO = T2.OO
> > WHERE (((H80.IDTraceH) In (1410,1488,1520)))
> > GROUP BY T2.A2IDSet, T2.OO, T2.DD
> > ORDER BY Avg(H71.YY), Avg(H71.CC))
> > AS T5
> > INNER JOIN
> > ( TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score
> > SELECT T2.A2IDSet, T2.OO, T2.DD
> > FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)
> > INNER JOIN T2 ON H71.OO = T2.OO
> > WHERE (((H80.IDTraceH) In (1410,1488,1520)))
> > GROUP BY T2.A2IDSet, T2.OO, T2.DD
> > ORDER BY T2.OO
> > PIVOT H80.IDTraceH)
> >
> > AS T4 ON T5.OO = T4.OO
> > ORDER BY T5.AvgOfYY, T5.AvgOfCC;
> >
> > When I try this, I get a " Syntax Error in FROM Clause" and TRANSFORM is
> > highlighted.
> >
> > I have pretty well decided to go with Option 2. But I thought I would
> ask:
> >
> > Does anyone have a why of making Option 4 work?
> >
> > Can anyone think of an option 5?
> >
> > Thank you for your time.
> >
> > Stephen Rasey
> > Houston
> > http://wiserways.com
> > http://excelsig.org
> >
> >
>
>

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: access queries
    ... In the first query, it still gives me back the GB and in the second query it ... TRANSFORM SumAS SumOfAmt_LclCurrency2 ... Alldocs.Relation FROM (((Alldocs INNER JOIN Accounts ON Alldocs.Account = ... Office-Location-Area].AREA FROM ((((Alldocs INNER JOIN Accounts ON ...
    (microsoft.public.access.queries)
  • cross join? full join? or something like that?
    ... I have this query ... TRANSFORM sum ... I get the other part of the table I need, and if I use INNER JOIN nothing ... appears, because I must get NULL values in the crossed reference table, so, ...
    (microsoft.public.access.queries)
  • Re: Problem in Cross tab query
    ... I created a cross tab query, which is running fine, as - ... FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN ... so I created a query where the transform part looks something like this - ...
    (microsoft.public.access.reports)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)