Bisarre join problem.

[Bob Quintal]

Have two tables linked from SQL server into Access '97, one
containing an autonumber primary key and the second table has a
text field populated with
cstr([autonumber]).

I can create a query that contains a calculated field
Cstr([autonumber]) and do an inner join on that to the second
table. I can also create a query containing a calculated field Clng
(textfield) and do an inner join to the first table.

But if I change the join to left or right, trying to view the query
gives me an error report "illegal join"

Any suggestions for a workaround, I really need a left join.

I am not allowed to alter the SQL server tables, and my bug reports
and suggestions for improvement are met with evil stares and worse
from the I.T. department personnel.

--
Bob Quintal

PA is y I've altered my email address.

[Rich P]

Assuming you don't have development rights on the sql server, like to
create stored procedures, what I would do would be to pull the data from
your ODBC table into a local table (or pull the portion of data you
would use - based on like a date range). Then work with that local
table. Access should be way more forgiving this way.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On Fri, 05 Aug 2005 22:15:20 GMT, Bob Quintal <rq******@sPAmp atico.ca>
wrote:

A join can always be rewritten as a whereclause. For example using the
Northwind sample application, these two queries are equivalent:
SELECT Orders.Customer ID, [Order Details].ProductID
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID;

SELECT Orders.Customer ID, [Order Details].ProductID
FROM Orders, [Order Details]
WHERE (((Orders.Order ID)=[Order Details].[OrderID] Or (Orders.OrderID )
Is Null));

-Tom.

Have two tables linked from SQL server into Access '97, one
containing an autonumber primary key and the second table has a
text field populated with
cstr([autonumber]).

I can create a query that contains a calculated field
Cstr([autonumber]) and do an inner join on that to the second
table. I can also create a query containing a calculated field Clng
(textfield) and do an inner join to the first table.

But if I change the join to left or right, trying to view the query
gives me an error report "illegal join"

Any suggestions for a workaround, I really need a left join.

I am not allowed to alter the SQL server tables, and my bug reports
and suggestions for improvement are met with evil stares and worse
from the I.T. department personnel.

[Tom van Stiphout]

On Fri, 05 Aug 2005 23:25:10 GMT, Rich P <rp*****@aol.co m> wrote:

But you would suffer terrible performance hits, assuming more than
trivial numbers of records.
-Tom.

Assuming you don't have development rights on the sql server, like to
create stored procedures, what I would do would be to pull the data from
your ODBC table into a local table (or pull the portion of data you
would use - based on like a date range). Then work with that local
table. Access should be way more forgiving this way.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Quintal]

Tom van Stiphout <no************ *@cox.net> wrote in
news:au******** *************** *********@4ax.c om:

On Fri, 05 Aug 2005 22:15:20 GMT, Bob Quintal
<rq******@sPAmp atico.ca> wrote:

A join can always be rewritten as a whereclause. For example
using the Northwind sample application, these two queries are
equivalent: SELECT Orders.Customer ID, [Order
Details].ProductID FROM Orders LEFT JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID;

SELECT Orders.Customer ID, [Order Details].ProductID
FROM Orders, [Order Details]
WHERE (((Orders.Order ID)=[Order Details].[OrderID] Or
(Orders.OrderID ) Is Null));

-Tom.
Thanks Tom,It works. I still wonder why an inner join doesn't
generate the same error message as the left join.

Have two tables linked from SQL server into Access '97, one
containing an autonumber primary key and the second table has
a text field populated with
cstr([autonumber]).

I can create a query that contains a calculated field
Cstr([autonumber]) and do an inner join on that to the second
table. I can also create a query containing a calculated field
Clng (textfield) and do an inner join to the first table.

But if I change the join to left or right, trying to view the
query gives me an error report "illegal join"

Any suggestions for a workaround, I really need a left join.

I am not allowed to alter the SQL server tables, and my bug
reports and suggestions for improvement are met with evil
stares and worse from the I.T. department personnel.

--
Bob Quintal

PA is y I've altered my email address.