Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: correlation with access gives a promt

    Hello there,

    I am learning T-SQL and in am using msaccess to test my t-sql.

    i am pulling my hair out on subqueries with correlation.

    i can't get this query to work in ms access

    SELECT Deelname.klnr
    FROM Deelname INNER JOIN Ronde AS R1 ON (Deelname.KARTNR = R1.KARTNR) AND (Deelname.HEATNR = R1.heatnr)
    GROUP BY Deelname.klnr
    HAVING Avg(R1.tijd)=(SELECT MIN(C)
    FROM
    ( SELECT AVG(r2.tijd) As C
    FROM RONDE AS R2
    WHERE (R2.KARTNR = R1.KARTNR)
    AND R2.heatnr = R1.heatnr))

    What i am doing wrong?

    regards
    Roger.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    First be aware that the Jet Engine (the SQL interpretor of Access) only "understands" a (quite large) subset of the T-SQL statements and grammar.

    Second, could you please post structural information and/or sample data about the tables (Deelname and Ronde)?
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    3

    did not know

    heatnr KARTNR rondenr tijd
    1 2 1 10
    1 2 2 11
    1 2 3 9
    1 2 4 8
    1 3 1 11
    1 3 2 13
    1 3 3 7
    3 2 1 8
    3 2 2 12
    3 5 1 16
    3 5 2 12
    4 3 1 15
    4 3 2 8
    4 3 3 6
    4 7 1 9
    4 7 2 10
    4 7 3 8

    KLNR HEATNR KARTNR
    1 1 2
    1 3 2
    2 1 3
    2 3 5
    3 4 3
    8 4 7

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you wish to use T-SQL inside Access it shouldn't be a problem, but make certain you use pass through queiries
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    what are "pass through queiries " ?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rogierm View Post
    what are "pass through queiries " ?
    How to create an SQL pass-through query in Access
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    In your query:
    Code:
    SELECT Deelname.klnr
    FROM Deelname INNER JOIN Ronde AS R1 
    ON (Deelname.KARTNR = R1.KARTNR) AND 
       (Deelname.HEATNR = R1.heatnr)
    GROUP BY Deelname.klnr
    HAVING Avg(R1.tijd)=(SELECT MIN(C)
                         FROM ( SELECT AVG(R2.tijd) As C
                                FROM RONDE AS R2 
                                WHERE (R2.KARTNR = R1.KARTNR) AND 
                                      (R2.heatnr = R1.heatnr)
                              )
                        );
    There is a reference to R2.tijd where R2 is an alias for RONDE ("...FROM RONDE AS R2..."). However and according to your sample data, the table RONDE is composed of the following columns: [KLNR], [HEATNR], [KARTNR] with no column named [tijd] (which is in the table Deelname).

    There also was a final closing parenthese missing.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •