Wrox Programmer Forums
|
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP 3.0 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 25th, 2009, 04:32 PM
Authorized User
 
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





Similar Threads
Thread Thread Starter Forum Replies Last Post
format report like spread sheet michael193nj Access 0 March 19th, 2008 05:17 PM
Create an Excel Spread with multiple worksheets hewstone999 Access VBA 1 March 7th, 2008 08:21 AM
SPREAD SHEET pallone Javascript How-To 12 June 27th, 2005 10:36 AM
Archiving excel spread sheet in zipped form Hudson40 Excel VBA 0 January 5th, 2005 12:33 PM
save Excel spread-sheet to SQL Server ybao Classic ASP Databases 1 July 12th, 2004 03:02 PM





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