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
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/
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.