Keith Williamson
Kwilliamson at RTKL.com
Fri Sep 25 13:47:14 CDT 2009
Anyone....Anyone......Bueller?? Bueller?? Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD 21231 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Friday, September 25, 2009 10:08 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel Query to SQL Database Thanks Charlotte, No. It does not work without the Transform, either. If I change to RIGHT outer join...I get the same error. Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD 21231 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, September 24, 2009 7:45 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Excel Query to SQL Database First question to ask is, does it run without the Transform? What happens if you change one of the LEFT OUTER JOINs to a RIGHT OUTER JOIN? Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Thursday, September 24, 2009 3:08 PM To: Access Developers discussion and problem solving Subject: [AccessD] Excel Query to SQL Database Can I get some help from someone on an Excel query of a SQL database....specifically a multi Left-Join? I've been working on this query for quite some time....and am sure the syntax is the issue...but can't figure it out. I have written the below query using EITHER of the Left-Joins...and it works fine. But when I try to use BOTH Left-Joins....I get a "Can't add table....." error. I'm hoping someone can spot the issue. ?? +++++++++++++++++++++++++++++++++++++++++++++++ Transform sum(qryARBals.Balance) SELECT qryARBals.Entity, qryARBals.WBS1, qryARBals.BillingCurrencyCode, qryProjInfo.ClientName, qryProjInfo.ClientType, qryProjInfo.FederalInd, qryProjInfo.MultAmt, qryProjInfo.RevType, qryRetainer.Retainer, sum(qryARBals.Balance) as [Balance], qryProjInfo.Center, qryProjInfo.Sector, qryLastPmt.TransDate, qryLastPmt.LastPmt (FROM (`V:\Finance\AR\2009\Aged AR Query`.qryARBals qryARBals inner join `V:\Finance\AR\2009\Aged AR Query`.qryProjInfo qryProjInfo on qryARBals.WBS1 = qryProjInfo.WBS1) LEFT OUTER JOIN `V:\Finance\AR\2009\Aged AR Query`.qryRetainer qryRetainer ON qryARBals.WBS1 = qryRetainer.WBS1) LEFT OUTER JOIN `V:\Finance\AR\2009\Aged AR Query`.qryLastPmt qryLastPmt ON qryARBals.WBS1 = qryLastPmt.WBS1 Group by qryARBals.Entity, qryARBals.WBS1, qryARBals.BillingCurrencyCode, qryProjInfo.ClientName, qryProjInfo.ClientType, qryProjInfo.FederalInd, qryProjInfo.MultAmt, qryProjInfo.RevType, qryRetainer.Retainer, qryProjInfo.Center, qryProjInfo.Sector, qryLastPmt.TransDate, qryLastPmt.LastPmt Pivot qryARBals.Age +++++++++++++++++++++++++++++++++++++++++++++++ The tables are qryARBals, qryProjInfo...which are inner-joined. qryRetainer and qryLastPmt ...I want to Left-Join to qryARBals on WBS1 Then, the whole thing is pivoted on qryARBals.Age If I remove either Left join (and the related fields)....it works. Thanks for any help. Regards, Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD 21231 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com <blocked::http://www.rtkl.com/> ------------------------------------------------------------------------ -- The information contained in this communication is confidential, may be privileged and is intended for the exclusive use of the above named addressee(s). If you are not the intended recipient(s), you are expressly prohibited from copying, distributing, disseminating, or in any other way using any of the information contained within this communication. If you have received this communication in error, please contact the sender by telephone at (410) 537-6000 or by response via e-mail and permanently delete the original email and any copies. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com