David L. Hansen
asked on
ACCESS SQL 'Syntax error in FROM clause' - Can you help me find the bug?
The following SQL Statement apparently has a typo. I can't find it though. It should be an easy 500 points. Here it is (remember it is Access):
SELECT DISTINCT VA.CallerID AS Client_ID, VB.[Date] AS Encounter, '1' AS Opt
FROM ((((qry_Disability_Referral_Counts AS VA
INNER JOIN qry_CallLog_Rel_CallType AS VB ON VB.Caller = VA.CallerID)
INNER JOIN qry_Calls_Rel_Vet_Services AS VV On VV.Call_ID = VB.Call_ID)
LEFT OUTER JOIN qry_War_Counts AS VW On VW.callerid = VA.CallerID)
LEFT OUTER JOIN qry_Race_Counts AS VR On VR.callerid = VA.CallerID)
LEFT OUTER JOIN qry_VA_Referral_ Counts AS VC ON VC.callerid = VA.CallerID
WHERE VB.Call_Type_ID = 2
UNION ALL
SELECT DISTINCT VA.CallerID AS Client_ID, VB.[Date] AS Encounter, '0' AS Opt
FROM ((((qry_Disability_Referral_Counts AS VA
INNER JOIN qry_CallLog_Rel_CallType AS VB ON VB.Caller = VA.CallerID)
INNER JOIN qry_Calls_Rel_Vet_Services VV On VV.Call_ID = VB.Call_ID)
LEFT OUTER JOIN qry_War_Counts AS VW On VW.callerid = VA.CallerID)
LEFT OUTER JOIN qry_Race_Counts AS VR On VR.callerid = VA.CallerID)
LEFT OUTER JOIN qry_VA_Referral_Counts AS VC ON VC.callerid = VA.CallerID
WHERE VB.Call_Type_ID <> 2;
I would start by removing all the parentheses
ASKER
It's Access. It is a requirement when working with multiple joins....see this link:
http://nm1m.blogspot.de/2007/10/multiple-left-joins-in-ms-access.html
I know...weird huh?
http://nm1m.blogspot.de/2007/10/multiple-left-joins-in-ms-access.html
I know...weird huh?
Ok, then it looks like you are missing a set for the final query in each part of your union
Are you able to use the Design view to build this query? It doesn't look too complicated to create and then you can use the SQL view to see exactly what Access wants.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I also notice in the link you provided that the first table is contained within parentheses but in your queries, they are not.
I think the from would then look like this with a 5th "(" and the ")" after the VA alias
FROM ((((qry_Disability_Referra l_Counts AS VA)
I think the from would then look like this with a 5th "(" and the ")" after the VA alias
FROM ((((qry_Disability_Referra
ASKER
You'd think huh? But just changing that one space made the whole thing work superbly...go figure.
ASKER
Found the bug on my own. Very grateful for someone looking at it with me though.