Outer Join problem

[Guest]

I have an Access query that uses a few outer-joins. When I run this query from my PC, I get 869 records but when I run it from another PC (the same database accessed through a shared folder), I get 1022 records! It looks as if my PC is unable to execute an outer-join and it is converting it to an inner-join before running. But there are other outer-join queries that work normally on my PC.

 

[Michel Walsh]

Hi,


Exactly the same query, with the same value for the parameters,
linked to exactly the same back end database? That is very un-usual.


Vanderghast, Access MVP

I have an Access query that uses a few outer-joins. When I run this query

from my PC, I get 869 records but when I run it from another PC (the same
database accessed through a shared folder), I get 1022 records! It looks as
if my PC is unable to execute an outer-join and it is converting it to an
inner-join before running. But there are other outer-join queries that work
normally on my PC.

 

[Michel Walsh]

Hi,


You have the SQL text of the query, just in case something would
become more obvious seeing it...? You are not using an additional WHERE
clause, when looking through the query, implying the 'unpreserved' table,
like:

SELECT ...
FROM a LEFT JOIN b ON ...
WHERE b.f1 = 222



that destroys the outer join effect; a proper handling could be:


SELECT ...
FROM a LEFT JOIN b ON ...
WHERE b.f1 = 222 OR b.f1 IS NULL






Vanderghast, Access MVP

It is the same MDB file accessed from different PCs through a shared

folder! There are no linked tables. The parameter entered is the same. I
even checked the Regional Settings for date formats - they are identical.
The MDAC used is 2.7 in both PCs. All DLL references are identical.

The interesting thing is, if I open this query using VBA or through a

report, I get all the records (1022). It is only when I open the query
directly, I get 869 records!

 

[Guest]

Hello

There is no WHERE condition in the query! In any case, should it make a difference on one PC and not on the other? The query (generated by Access) is as follows

SELECT
FROM ((([6-q-listing00-turin-oldTIS]

LEFT JOIN T_A_TIS_COMM ON ([6-q-listing00-turin-oldTIS].datevalidity = T_A_TIS_COMM.[Date-validity]) AND ([6-q-listing00-turin-oldTIS].BU = T_A_TIS_COMM.BU))

LEFT JOIN T_A_TIS_topup ON ([6-q-listing00-turin-oldTIS].BU_pricelist = T_A_TIS_topup.BU) AND ([6-q-listing00-turin-oldTIS].datevalidity = T_A_TIS_topup.[Date-validity]))

LEFT JOIN [6-t-final-virtual] ON ([6-q-listing00-turin-oldTIS].datevalidity = [6-t-final-virtual].virtualdate) AND ([6-q-listing00-turin-oldTIS].Body = [6-t-final-virtual].Body) AND ([6-q-listing00-turin-oldTIS].Model = [6-t-final-virtual].Model) AND ([6-q-listing00-turin-oldTIS].BU_pricelist = [6-t-final-virtual].BU_pricelist))

LEFT JOIN [6-t-stream-percentage] ON ([6-q-listing00-turin-oldTIS].Condition = [6-t-stream-percentage].Condition) AND ([6-q-listing00-turin-oldTIS].datevalidity = [6-t-stream-percentage].[Date-validity]) AND ([6-q-listing00-turin-oldTIS].BU_pricelist = [6-t-stream-percentage].BU)

Thanks

 

[Michel Walsh]

Hi,


I don't see any problem in the SQL statement itself.

I would then check to see if the tables "links" are the good ones (tools
| database utilities... Linked Table Manager) and if they are, then, I would
try to see if the same problem exists when the PC role is exchanged ( make a
copy of the back end on the second PC, run the application from the first
PC, with the data now on the second PC ). If the PC that was reporting 1022
records still report 1022 records and if the other still report 869 records,
that would strongly indicate some setting, but which one exactly ... (
date_time, time zone, Jet version and ServicePack and Access options... )
Are they all tables or some are queries and so, are they returning the same
number of records on both PC.

To see if the problem is really due to the network, it is probably to
run the front end application on the same PC that has the back end
application (copy the front end, or the back end file, appropriately, and
re-edit the link of the linked table). If the number of records they report
is unchanged, something is definitively wrong with either of the PC, since
no network is involved, and both PC work on an identical copy of data!




Vanderghast, Access MVP

Hello,

There is no WHERE condition in the query! In any case, should it make a

difference on one PC and not on the other? The query (generated by Access)
is as follows:

SELECT *
FROM ((([6-q-listing00-turin-oldTIS]

LEFT JOIN T_A_TIS_COMM ON ([6-q-listing00-turin-oldTIS].datevalidity =

T_A_TIS_COMM.[Date-validity]) AND ([6-q-listing00-turin-oldTIS].BU =
T_A_TIS_COMM.BU))

LEFT JOIN T_A_TIS_topup ON ([6-q-listing00-turin-oldTIS].BU_pricelist =

T_A_TIS_topup.BU) AND ([6-q-listing00-turin-oldTIS].datevalidity =
T_A_TIS_topup.[Date-validity]))

LEFT JOIN [6-t-final-virtual] ON

([6-q-listing00-turin-oldTIS].datevalidity =
[6-t-final-virtual].virtualdate) AND ([6-q-listing00-turin-oldTIS].Body =
[6-t-final-virtual].Body) AND ([6-q-listing00-turin-oldTIS].Model =
[6-t-final-virtual].Model) AND ([6-q-listing00-turin-oldTIS].BU_pricelist =
[6-t-final-virtual].BU_pricelist))

LEFT JOIN [6-t-stream-percentage] ON

([6-q-listing00-turin-oldTIS].Condition = [6-t-stream-percentage].Condition)
AND ([6-q-listing00-turin-oldTIS].datevalidity =
[6-t-stream-percentage].[Date-validity]) AND
([6-q-listing00-turin-oldTIS].BU_pricelist = [6-t-stream-percentage].BU);