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

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



rudy
http://r937.com/

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