View Single Post
  #1 (permalink)  
Old June 25th, 2009, 04:32 PM
hddavie hddavie is offline
Authorized User
Points: 73, Level: 1
Points: 73, Level: 1 Points: 73, Level: 1 Points: 73, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2009
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
Default connecting to an excel spread sheet

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