p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning ASP 3.0 (http://p2p.wrox.com/forumdisplay.php?f=27)
-   -   connecting to an excel spread sheet (http://p2p.wrox.com/showthread.php?t=74952)

hddavie June 25th, 2009 04:32 PM

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


All times are GMT -4. The time now is 11:50 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.