I am trying to generate a web based summary report from a Excel spreadsheet. I am having a problem with outer joins. I end up with an error when I have more that one outer join. I am basing this code on a similar report I did from a SQL Server db, that had 7 outer joins. Here is what I started with. This code works;
Code:
SELECT CntTamperBottom, CntAccelerateZ FROM(Select [Operating Mode],Count(*) AS CntTamperBottom FROM fieldFailures WHERE [Breach Reason History 0:] like 'Tamper Bottom' AND [Operating Mode] like 'Breached' GROUP BY [Operating Mode])aTab left outer join (SELECT [Operating Mode], Count(*) AS CntAccelerateZ FROM fieldFailures WHERE [Breach Reason History 0:] like 'Accelerate Z' AND [Operating Mode] like 'Breached' GROUP BY [Operating Mode])bTab on bTab.[Operating Mode] = aTab.[Operating Mode];
Here is the code that fails;
Code:
SELECT CntTamperBottom, CntAccelerateZ, CntAccelerateY FROM(Select [Operating Mode],Count(*) AS CntTamperBottom FROM fieldFailures WHERE ([Breach Reason History 0:] like 'Tamper Bottom' AND [Operating Mode] like 'Breached') GROUP BY [Operating Mode])aTab left outer join (SELECT [Operating Mode], Count(*) AS CntAccelerateY FROM fieldFailures WHERE ([Breach Reason History 0:] like 'Accelerate Y' AND [Operating Mode] like 'Breached') GROUP BY [Operating Mode]) cTab on cTab.[Operating Mode] = aTab.[Operating Mode] left outer join (SELECT [Operating Mode], Count(*) AS CntAccelerateZ FROM fieldFailures WHERE ([Breach Reason History 0:] like 'Accelerate Z' and [Operating Mode] like 'Breached') Group By [Operating Mode])bTab on bTab.[Operating Mode] = aTab.[Operating Mode];
And this is the error message;
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'cTab.[Operating Mode] = aTab.[Operating Mode] left outer join (SELECT [Operating Mode], Count(*) AS CntAccelerateZ FROM fieldFailures WHERE ([Breach Reason History 0:] like 'Accelerate Z''.
Any help would be appreciated.
Thanks
Davie
connecting to an excel spread sheet
