[AccessD] Join expression not supported

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




More information about the AccessD mailing list