inner join/where query fails without an error

[neonx3]

Hi,

I'm running Access 2000.

I have a (messy) query which outputs Part_No and Voltage2, and Access
will display the datasheet view without a problem. Part_No is unique
in this result.

I want to join it with another table which has a field with PART_NO.
I'm using this statement:
SELECT qEV.voltage2
FROM Electrical AS EL INNER JOIN qryEquipVoltage AS qEV ON EL.EQUIP_NO
= qEV.EQUIP_NO;

and it fails by not doing anything (visible) when I click on the
"datasheet view" button. After doing this, I cannot close related
tables/queries because I get a "This action will reset the current code
in break mode" error which clicking "Yes" does not clear away.
However, I can close & save the query which failed.

If I change the query to:
SELECT qEV.EQUIP_NO
FROM Electrical AS EL INNER JOIN qryEquipVoltage AS qEV ON EL.EQUIP_NO
= qEV.EQUIP_NO;

then it works fine, but I don't get the information I need. Voltage2,
which I'm trying to display, is determined using information from
several different tables including "Electrical" - perhaps this is the
problem?

Thanks in advance,
Danny

 

[neonx3]

Changing the query to a RIGHT or LEFT JOIN solves the problem (except
that I'd should have an inner join), but I'm not clear on why. Can
someone explain this to me?

Thanks,
Danny

 

[Michel Walsh]

Hi,

****************
A CROSS join matches all records of table a {f1, f2} with all records of
table b {f1, f2}. If table a has 3 records{{a, 1}, {a, 2}, {a,3}} and table
b has 2 records {{b, 1}, {b, 2}} then the result has 6 records:

a, 1, b, 1
a, 1, b, 2
a, 2, b, 1
a, 2, b, 2
a, 3, b, 1
a, 3, b, 2


ie, you can find a row, in this result, that owns any row you can pick from
a and any row you pick from b,



*****************
An INNER join add a condition (like a WHERE, but expressed in the join after
the keyword ON ) and ONLY the result form the CROSS join that passes the ON
condition are kept. As example, with

ON a.f2 <= b.f2

only 3 rows will be returned, since {a, 2} {b, 1} does not pass the test,
neither {a, 3}{b,1}, neither {a, 3}{b,2}.




a, 1, b, 1
a, 1, b, 2
a, 2, b, 2

*****************
An OUTER join is like an INNER join but IF a row, from the preserved table,
completely disappear, it is re-introduced, with NULL under the columns of
the other table.

FROM a LEFT JOIN b ON a.f2<=b.f2

preserve table a (because it is the one mentioned on the LEFT side of the
word JOIN; with a RIGHT JOIN b that would be table b that is
preserved).

BUT since the row {a, 3} has disappeared from the inner join, the LEFT
join re-introduced it:


a, 1, b, 1
a, 1, b, 2
a, 2, b, 2
a, 3, -, -


(where I used - in place of a NULL).


You can take on yourself to see than FROM a RIGHT JOIN b ON a.f2<=b.f2 is,
here, identical to the inner join, since no rows, in b, had completely
disappeared.



*****************
A FULL OUTER join, not directly supported by Jet, allows to preserved BOTH
tables.





*****************
An UNION join, not supported directly by Jet, neither by MS SQL Server,
produces something like:

a, 1, -, -
a, 2, -, -
a, 3, -, -
-, -, b, 1
-, -, b, 2


which, in MS SQL Server, can be accomplished supplying a FULL OUTER JOIN
with a ON clause that is always false.




*****************
Final remarks:

the WHERE clause logically occurs always after the JOIN (logically) and
before any GROUP or aggregate.

what is described, here up, is the intended result. The database engine
can take short-cuts, that is, it may proceed differently to reach the
result, and not use the technique we used to ILLUSTRATE the logical intended
result, as long as the result IS the same as if the long method (we used to
illustrate) had been used.

As example,

SELECT COUNT(*)
FROM a, b


which is a cross join between the two tables, so, you now know that the
result can be computed by a simple multiplication of the number of rows in a
by the number of rows in b, ... without making any join at all.




Hoping it may help,
Vanderghast, Access MVP

 

[Daniel]

Thanks for your input Vanderghast,

If anyone encounters the same problems I'm having, although I still
can't really explain what logic there is behind the inner join not
working, the problem can be traced back (I think) to using a subquery
("SQ1") to get the value of a particular field ("P1") within the SELECT
statement of another query ("Q1") . Subsequent attempts to include P1
in another query ("Q2") will cause Q2 to be read-only if it is
left/right outer join. However, if it is an inner join, then sometimes
it seems to fail entirely without giving an error message.

Daniel