Kaup, Chester
Chester_Kaup at kindermorgan.com
Mon Feb 19 13:22:15 CST 2007
The query posted below is from SQL view and unfortunately it gives no good error message. I am thinking it is just a little to complex for Access to evaluate all at one time. I am going to break it into more than one query. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Monday, February 19, 2007 1:11 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Join expression not supported Try opening it in SQL view instead of design view. Offhand, I don't see the problem, but this will enable you to inspect the syntax closely, and running it from there might give you a better error message. I would also try creating a query for each table that creates a calculated column encompassing your Right() expression, etc. This might be the problem. With a calculated column the join would be straightforward and more amenable to Access's syntax checker. Also your WHERE seems unncecessarily complex. I might consider creating a calculated column for it as well. Arthur Fuller Technical Writer, Data Modeler, SQL Sensei Artful Databases Organization www.artfulsoftware.com ----- Original Message ---- From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Monday, February 19, 2007 1:21:48 PM Subject: Re: [AccessD] Join expression not supported No it does not run. Just returns error message. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Monday, February 19, 2007 11:40 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Join expression not supported Does it run? You just can't view a join like this in the query builder in Access. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Monday, February 19, 2007 11:30 AM To: Access Developers discussion and problem solving Subject: [AccessD] Join expression not supported I an getting a join expression not supported error from the following query. I am not seeing the problem. Thanks SELECT dbo_DSS_CompletionMaster.PID, Right(ConfigMaster.PID,Len(ConfigMaster.PID)-4) AS WellName FROM ConfigMaster, dbo_DSS_CompletionMaster LEFT JOIN ConfigMaster ON Right([ConfigMaster].[PID],Len([ConfigMaster].[PID])-4))= dbo_DSS_CompletionMaster.Well_Number WHERE (((ConfigMaster.PID) Like "PAT*") AND ((Right([ConfigMaster].[PID],Len([ConfigMaster].[PID])-4)) Not Like "*GAP") GROUP BY dbo_DSS_CompletionMaster.PID, Right(ConfigMaster.PID,Len(ConfigMaster.PID)-4); Chester Kaup Engineering Technician Kinder Morgan CO2 Company, LLP Office (432) 688-3797 FAX (432) 688-3799 No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced. -- 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com