#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    sql syntax help please


    my connection is a remote dnsless ms access database.
    i have four tables to query by way of sql.
    i've got a cold fustion page using cfquery:

    <CFQUERY NAME="The_Team_Summary" Datasource="#thisdatasource#"
    username="this" PASSWORD="that">

    SELECT tbl_TheTeams.TeamNumber AS Team_ID, tbl_TheTeams.TeamName AS Team_Name, Sum(tbl_TheData.TheSteps) AS XSteps, Count(tbl_LegWinner.TheWinner) AS Legs_Won
    FROM tbl_LegWinner INNER JOIN (tbl_CurrentLeg INNER JOIN (tbl_TheTeams INNER JOIN tbl_TheData ON tbl_TheTeams.TeamNumber = tbl_TheData.TheTeam) ON tbl_CurrentLeg.txt_LegID = tbl_TheData.TheLegID) ON tbl_LegWinner.TheLeg = tbl_CurrentLeg.txt_LegID
    GROUP BY tbl_TheTeams.TeamNumber, tbl_TheTeams.TeamName;

    The connection itself works fine for simple one table queries.
    When i cut and paste the sql from access, i have to edit the
    code alittle bit: The FROM clause needs the path added to it like this: FROM 'D:\thepath\thedb.mdb' and the table name needs a (.)DOT added so i wind up with:
    FROM 'D:\thepath\thedb.mdb'.thetablename (then the rest of the statement)...

    My problem at the top of the page is i have a four tables, some groupon, some sum, some count. Cut & Paste SQL again needs to be edited, and I tried and failed to get it right.

    Anyone know how to sytax several inner joins?
    The access error message is:

    Error Diagnostic Information
    ODBC Error Code = 37000 (Syntax error or access violation)

    [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

    SQL = "SELECT tbl_TheTeams.TeamNumber AS Team_ID, tbl_TheTeams.TeamName AS Team_Name, Sum(tbl_TheData.TheSteps) AS XSteps, Count(tbl_LegWinner.TheWinner) AS Legs_Won FROM `D:\pathtodb\TheRace.mdb` tbl_LegWinner INNER JOIN (tbl_CurrentLeg INNER JOIN (tbl_TheTeams INNER JOIN tbl_TheData ON tbl_TheTeams.TeamNumber = tbl_TheData.TheTeam) ON tbl_CurrentLeg.txt_LegID = tbl_TheData.TheLegID) ON tbl_LegWinner.TheLeg = tbl_CurrentLeg.txt_LegID GROUP BY tbl_TheTeams.TeamNumber, tbl_TheTeams.TeamName"

    Data Source = "thissource"

    The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (4:36).

    Thanks,
    ddollar
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,240
    Rep Power
    4279
    i've never had to use file paths, but from your description, where you say you have to stuff a dot between the path and the table name...

    ... you don't have a dot in the sql that's causing an error

    you have

    FROM `D:\pathtodb\TheRace.mdb` tbl_LegWinner

    also, i wouldn't use those mysql-style backticks, i'd use singlequotes

    plus, i's alias the table names

    Code:
    SELECT tbl_TheTeams.TeamNumber AS Team_ID
         , tbl_TheTeams.TeamName AS Team_Name
         , Sum(tbl_TheData.TheSteps) AS XSteps
         , Count(tbl_LegWinner.TheWinner) AS Legs_Won 
      FROM 'D:\pathtodb\TheRace.mdb'.tbl_LegWinner   W
    INNER 
      JOIN (
           tbl_CurrentLeg C
    INNER
      JOIN (
           tbl_TheTeams T
    INNER 
      JOIN tbl_TheData D
        ON T.TeamNumber = D.TheTeam
           ) 
        ON C.txt_LegID = D.TheLegID
           ) 
        ON W.TheLeg = C.txt_LegID 
    
    GROUP BY tbl_TheTeams.TeamNumber
         , tbl_TheTeams.TeamName
    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Central CT USA
    Posts
    2
    Rep Power
    0

    Lightbulb


    In MS Access, try formatting your SQL syntax this way:




    FROM

    [D:\pathtodb\TheRace.mdb].tbl_LegWinner




    ie, use square brackets, not single quotes, around your pathname.

    Good luck!

    --Brian



IMN logo majestic logo threadwatch logo seochat tools logo