JWColby
jwcolby at colbyconsulting.com
Mon Feb 19 13:44:15 CST 2007
Join expression not supported often means that the data types on the two sides of the join are not the same. I would do as you are suggesting, and break it down into several queries. Create a base query for: Right(ConfigMaster.PID,Len(ConfigMaster.PID)-4) AS WellName FROM ConfigMaster So that the alias has been evaluated and valid values are being returned (strings it appears). Once that is in a query as a simple string value, you can join that to another query or table on a string field. It appears that the where clause is also on the configMaster table and could be moved out to that base query as well. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Monday, February 19, 2007 2:22 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Join expression not supported 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com