Left Join behaving like Inner Join - Access 97

[jason.evans]

Hi there.

I am having an intrigueing problem.

I have a query which left joins another query to itself twice. The
original query is derived from a linked table in SQLServer 2000.

When I run it on my pc It runs fine. However for other users in the
office, it behaves as an inner join. ie it only returns the records fo
which the join fields equal each other. This happens on every other pc
I log into.

I have a couple of pcs with Access 2000 installed on it. When I convert
the database and run it on those pcs I also have no problem.

Any advice much appreciated.

[Allen Browne]

Jason, this sounds fairly unusual.

First thing to check is whether the users who do have the problem are adding
criteria to the query. If you use criteria on the fields from the outer side
of the join, it will behave as an inner join because the nulls are
eliminated.

You say the problem does not occur on your computer, but it does no others.
You are therefore seeking to pin down what is different between the
computers that do show the problem consistently, and those that don't.
Presumably your machine has both Access 97 and 2000 on it. Is that the
difference between yours and the others? Is this query being executed in
code, or run from the query window? If code, which DAO library are you
referencing? (It should be 3.51, but you could (incorrectly) end up with 3.6
referenced if the database was converted back from a later version, and that
could be the difference.)

A2000 and later do use a different query engine (JET 4 verses JET 3.5 in
A97), so it could theoretically behave differently, but I have not seen
anything like what you describe.

There is a case where JET wrongly treats an outer join as an inner join. It
sounds quite different to your symptom, and is not version dependent AFAIK.
Details in:
Records missed by SELECT query
at:
http://allenbrowne.com/bug-10.html

Is there any chance that Access could misundertand the data types of the
join or criteria? Typically this can happen where literals or calculated
fields are involved, and JET 4 certainly does behave differently than JET
3.5 in this area. (It seems less capable of identifying the correct type
IME.) If that sounds like it could apply to you, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ja*********@minterellison.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a query which left joins another query to itself twice. The
original query is derived from a linked table in SQLServer 2000.

When I run it on my pc It runs fine. However for other users in the
office, it behaves as an inner join. ie it only returns the records fo
which the join fields equal each other. This happens on every other pc
I log into.

I have a couple of pcs with Access 2000 installed on it. When I convert
the database and run it on those pcs I also have no problem.

Any advice much appreciated.

[jason.evans]

Thanks very much for the reply Allen.

Firstly, the users are not adding any criteria to the query at all.

My computer does not have Access 2000 on it. I have however run the
query on another computer with both 97 and 2000 installed and it works
fine in 2000 but doesnt work in 97.

The query is just being run from the query window, not in code.

Finally, I have no literals or calculated fields in the query.

This is something I have not noticed happen before and certainly in the
case of this database, it used to work fine. I have users who have run
reports based on this query and printed them out, so the hard copies
they have verify the fact that it once worked.

It is indeed very odd

[jason.evans]

Further to my problem, I did a test whereby I imported the table data
from SQL Server into Access and recreated my query using the imported
table rather than the linked table.

In this case the query works fine. This does not solve my problem
because the live data is in SQLServer, however does maybe help to
narrow where the issue lies.

What I am now puzzling over is what possible difference could there be
between my link to the SQLServer and others.

Also why this problem has not previously been an issue.

[jason.evans]

Wow. I found the answer. Its taken some time but the effort worth it.

http://support.microsoft.com/kb/q225422/

I have installed service pack 3 for Jet3.5 and it has resolved the
issue.

Our IT dept has recently rolled out a new SOE. My suspicion is that
they applied patches in the previous SOE (hence why this problem hadnt
surfaced before) but didnt apply it to the new image.

I shall have to see if they can push this service pack out firm wide.

Thanks for all the help

[Allen Browne]

Good trouble-shooting, Jason.

And thanks for posting the solution, so others who are searching the
archives at groups.google.com can find the answer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ja*********@minterellison.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Wow. I found the answer. Its taken some time but the effort worth it.

http://support.microsoft.com/kb/q225422/

I have installed service pack 3 for Jet3.5 and it has resolved the
issue.

Our IT dept has recently rolled out a new SOE. My suspicion is that
they applied patches in the previous SOE (hence why this problem hadnt
surfaced before) but didnt apply it to the new image.

I shall have to see if they can push this service pack out firm wide.

Thanks for all the help