MS Access SQL Syntax Error

There is a JOIN syntax error in this SQL, but my slow brain cannot figure out where. I tried to join two queries, which had been successful. Problem occurred when I added second left join. Can anyone help?

PARAMETERS pstrFinYear Text ( 255 ), pintAdjMonth Long;

SELECT A.BudgetLineID, A.BudgetLine, B.NumIsDevelopBusinessInternationally, B.NumIsDeeperParticipation, B.NumIsNewExporter, B.NumProjects, C.NumCompanies, A.KMISReportOrder
FROM ( tblkpBudgetLine AS A

LEFT JOIN

[SELECT BudgetLineID, FinancialYear, SUM(IsDevelopBusinessInternationally) AS NumIsDevelopBusinessInternationally, SUM(IsDeeperParticipation) AS NumIsDeeperParticipation, (-1*SUM(AdjustedNewExpMonth=pintAdjMonth)) AS NumIsNewExporter, COUNT(ProjectID) AS NumProjects
FROM qryBoardReport_Actuals
WHERE FinancialYear=pstrFinYear
AND AdjustedProjectStartMonth=pintAdjMonth
GROUP BY BudgetLineID, FinancialYear]. AS B
ON (A.BudgetLineID=B.BudgetLineID) AND (A.FinancialYear=B.BudgetLine)

LEFT JOIN

[SELECT Z.BudgetLineID, Z.FinancialYear, Z.AdjustedProjectStartMonth, COUNT(Z.Company) AS NumCompanies
FROM [SELECT DISTINCT qryBoardReport_Actuals.BudgetLineID AS BudgetLineID, qryBoardReport_Actuals.FinancialYear AS
FinancialYear, qryBoardReport_Actuals.AdjustedProjectStartMonth, qryBoardReport_Actuals.CompanyID as Company
FROM qryBoardReport_Actuals
WHERE FinancialYear=pstrFinYear
AND AdjustedProjectStartMonth=pintAdjMonth
GROUP BY qryBoardReport_Actuals.FinancialYear, qryBoardReport_Actuals.BudgetLineID, qryBoardReport_Actuals.AdjustedProjectStartMonth, qryBoardReport_Actuals.CompanyID]. AS Z
GROUP BY FinancialYear, BudgetLineID, AdjustedProjectStartMonth] as C

ON (A.BudgetLineID=C.BudgetLineID) and (A.FinancialYear=C.FinancialYear))


WHERE A.FinancialYear=pstrFinYear
ORDER BY A.KMISReportOrder;

In my limited experience w/MS Access...I don't think you can join table A to table B, then join table A to Table C. I think you have to join table A to (Table B join Table C), see what I'm saying...you have this going on.

Give this a try...I think you need to join the "farthest" tables first inside parens, tableB and tableC, and then add your syntax to complete the join on TableA to TableB. The above where you had table A joined to table B joined to table C all in one set of parens, I don't think Access allows that. Access is really picky where parens go, and even more so w/parenthetical matching w/queries that use an exaggerated # of joins. Try this psuedo query though...My apologies if this is incorrect.

here's an example w/a query using a 5 table join (same syntax regaurdless of left, right, full joins)...note that w/every new table joined, a parenthetical match is required...

here's a link that gives a pretty good example on how to nest joins in Access. The example is near the bottom of the pg.

http://www.yk.psu.edu/~lxn/IST_210/...nner_joins.html

be careful with what you read on some web sites

for example, the above-mentioned site contains this gem:
patently wrong

a silly, trivial, human error, but wrong nonetheless

Will sort out parenthesis and joins.

Thanks for your help.

I have a similar problem. I'm not sure what I want to do is even possible using Access, though it does fit within SQL standards.

My situation is complex, but this is a simple example:

Table T1 links to table T2 via fields a1 = a2
T1 links to table T3 via fields b1 = b3
T1 links to table T4 via fields c1 = c4

T2, T3 and T4 may not have records associated with any given record in T1, and T2, T3 and T4 are not related to each other directly at all.

If I want to pull a record from T1 with all associated records (if any) from T2, T3, and T4, I must use LEFT JOINs. Standard SQL would have me do it thus:

SELECT * FROM T1 LEFT JOIN T2 ON T1.a1 = T2.a2 LEFT JOIN T3 ON T1.b1 = T3.b3 LEFT JOIN T4 ON T1.c1 = T4.c4

But that, of course, does not work on Access. In fact, there is no method that I can find to make it work with their screwy parenthesized nested joins. Can anyone clear that up for me?

actually, if T2, T3, and T4 are unrelated, what you want is UNIONs

t1 join t2
union
t1 join t3
union
t1 join t4

I'm having a similar issue I was hoping someone here could shed some light on.

SELECT *
FROM ((((((SK_SKILLSINAGES AS sia INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON sia.GroupCode = cg.GroupCode) INNER JOIN SK_CATEGORY AS c ON (sia.CategoryCode = c.CategoryCode) AND (sia.GroupCode = c.GroupCode)) INNER JOIN SK_SKILLS AS s ON sia.SkillCode = s.SkillCode) LEFT JOIN SK_SUBSKILLS AS ss ON (sia.SkillCode = ss.SkillCode AND sia.SubSkillCode = ss.SubSkillCode) OR (sia.SkillCode = ss.SkillCode AND ss.Table = -1)) LEFT JOIN Query2 AS uq ON uq.Table = ss.SubSkillCode AND uq.Code = sia.SubSkillCode
ORDER BY e.Description, ae.Description, cg.Description, c.Description, s.Description, ss.Description;

Yes, a complicated SQL query, but necessary nonetheless. The issue I'm having is with the code in DarkRed. Apparently if I removed the line, the query works in Access. Keeping the line I get a join expression not supported. It seems removing the AND and the the joining statement allows Access to parse it. I could use either A or B as a statement and have it work but not together. Is there something I'm missing? I checked out the site and I could try it using that syntax but it seems the syntax I am using works it's just that when joining A and B together is when it breaks.

Thanks!

Access parenthesis mania. Try something like

nope... darn.... here's an interesting twist.



works!

Which tells me perhaps there is a disconnect in my joins. What I don't understand is I call the table given the subname "sia" in the join just before this one without a problem. Query2 is a UNION query so I replaced it with just a simple table, and it still doesn't like it, so it is definitely a disconnent somewhere. I have a have a feeling I'm inches from the solution but just don't see it.

edit: If I change LEFT JOIN (both of them only) to INNER JOIN it works. Unfortunately LEFT JOIN is important.