Editing disabled by alternative to INNER JOIN

[cjakeman]

Hi,

Solved a little mystery yesterday when I built a form that combined 2
tables with a 1:M relationship and relational integrity. All the
correct data was visible on the form but, if I tried to edit any of
the fields, the PC bleeped.

Seems it was due to the query the form was based on. Again, editing
any of the fields of the query caused the PC to bleep.

The problem lay in the SQL behind the query. I'm more comfortable with
SQL than the graphical Design View (in fact I teach classes in SQL).
If you let Access build a query, it will give you:

SELECT * FROM teachers INNER JOIN subjects ON
teachers.name=s ubjects.taught_ by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name=s ubjects.taught_ by;

The query results are identical, but Access is no longer willing to
write back the edits to the right tables. I guess the INNER JOIN
construction makes possible some safety checking.

I've learned a lesson and perhaps posting this message might help
someone else one day.

Bye for now,

Chris

['69 Camaro]

Hi, Chris.

SELECT * FROM teachers INNER JOIN subjects ON
teachers.name=s ubjects.taught_ by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name=s ubjects.taught_ by;

These two queries are _not_ equivalent in Jet (Access). The second query
uses a Cartesian Join, instead of an ANSI join. A Cartesian Join matches
every row in one table to every row in the other table (even when there is
no logical correlation). A query that uses a Cartesian Join is not
updateable, that's why you hear the beeps. The fact that you placed a WHERE
clause narrowed the results so that the results of the two queries look
identical. However, if you remove the WHERE clause in both queries and then
compare the results, you'll see two very different result sets. Only the
WHERE clause hides the effects of the Cartesian Join (except for the slower
speed when running the query).

I guess the INNER JOIN
construction makes possible some safety checking.

The INNER JOIN allows updateable recordsets, and is much more efficient in
Jet queries. It's easy to run out of resources when using a Cartesian Join
with larger data sets. They take a longer time for Jet to run than when
using an INNER JOIN, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[David W. Fenton]

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:jFXsi.228$ V53.2@trnddc08:

Hi, Chris.

>SELECT * FROM teachers INNER JOIN subjects ON
teachers.name= subjects.taught _by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name= subjects.taught _by;

These two queries are _not_ equivalent in Jet (Access). The
second query uses a Cartesian Join, instead of an ANSI join.

You you checked SHOWPLAN on this? I have, and it shows that a JOIN
statement converted to a WHERE clauses is optimized exactly the
same. In this case, the WHERE clause and JOIN is identical, so it
would be treated exactly the same by Jet.

A Cartesian Join matches
every row in one table to every row in the other table (even when
there is no logical correlation).

There will be no Cartesian join in either case, unless there's no
index on the fields in the JOIN/WHERE statement.

A query that uses a Cartesian Join is not
updateable, that's why you hear the beeps. The fact that you
placed a WHERE clause narrowed the results so that the results of
the two queries look identical. However, if you remove the WHERE
clause in both queries and then compare the results, you'll see
two very different result sets. Only the WHERE clause hides the
effects of the Cartesian Join (except for the slower speed when
running the query).

I don't know about editability, but Jet optimizes the two queries
exactly the same way.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Arch]

On Sat, 04 Aug 2007 09:28:47 GMT, "'69 Camaro"
<Fo************ **************@ Spameater.orgZE RO_SPAMwrote:

>Hi, Chris.

>SELECT * FROM teachers INNER JOIN subjects ON
teachers.name= subjects.taught _by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name= subjects.taught _by;

These two queries are _not_ equivalent in Jet (Access). The second query
uses a Cartesian Join, instead of an ANSI join. A Cartesian Join matches
every row in one table to every row in the other table (even when there is
no logical correlation). A query that uses a Cartesian Join is not
updateable, that's why you hear the beeps. The fact that you placed a WHERE
clause narrowed the results so that the results of the two queries look
identical. However, if you remove the WHERE clause in both queries and then
compare the results, you'll see two very different result sets. Only the
WHERE clause hides the effects of the Cartesian Join (except for the slower
speed when running the query).

>I guess the INNER JOIN
construction makes possible some safety checking.

The INNER JOIN allows updateable recordsets, and is much more efficient in
Jet queries. It's easy to run out of resources when using a Cartesian Join
with larger data sets. They take a longer time for Jet to run than when
using an INNER JOIN, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

I don't believe that this particular example is a "Cartesian Join".
Both forms of the query create a valid ANSI join and are valid ANSI
syntax. The first example uses the newer SQL/92 syntax, the second
uses the older SQL/88 syntax.

[David W. Fenton]

Bob Quintal <rq******@sPAmp atico.cawrote in
news:Xn******** **************@ 66.150.105.47:

The where clause approach is still the only one supported by
some major databases, and the Access query builder does not
allow a join on a.field < b.field.

Sure it does. You just have to edit it in SQL view and then not try
to view it with the query designer.

There are also cases in Access where you can't use a join where a
WHERE clause will work. I have to do this frequently with "joins" on
GUID fields in the Jet replication tables. In that case,
updatability is moot, as the tables are not themselves updatable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in
news:Xn******** *************** ***********@127 .0.0.1:

Bob Quintal <rq******@sPAmp atico.cawrote in
news:Xn******** **************@ 66.150.105.47:

>The where clause approach is still the only one supported by
some major databases, and the Access query builder does not
allow a join on a.field < b.field.

Sure it does. You just have to edit it in SQL view and then
not try to view it with the query designer.

Try not to view it.... how can one open a query directly to SQL
mode, instead of design view?

>
There are also cases in Access where you can't use a join
where a WHERE clause will work. I have to do this frequently
with "joins" on GUID fields in the Jet replication tables. In
that case, updatability is moot, as the tables are not
themselves updatable.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Wayne Gillespie]

On 06 Aug 2007 00:17:30 GMT, Bob Quintal <rq******@sPAmp atico.cawrote:

>Try not to view it.... how can one open a query directly to SQL
mode, instead of design view?

If you save the query while in SQL view the query will open at the SQL view when
design is selected from the database window, not the "standard" design view.
Wayne Gillespie
Gosford NSW Australia

[Bob Quintal]

Wayne Gillespie <be*****@NOhotm ailSPAM.com.auw rote in
news:ah******** *************** *********@4ax.c om:

On 06 Aug 2007 00:17:30 GMT, Bob Quintal
<rq******@sPAmp atico.cawrote:

>>Try not to view it.... how can one open a query directly to
SQL mode, instead of design view?

If you save the query while in SQL view the query will open at
the SQL view when design is selected from the database window,
not the "standard" design view.
Wayne Gillespie
Gosford NSW Australia

I wish it would...
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Arno R]

"Bob Quintal" <rq******@sPAmp atico.caschreef in bericht news:Xn******** **************@ 66.150.105.47.. .

Wayne Gillespie <be*****@NOhotm ailSPAM.com.auw rote in
news:ah******** *************** *********@4ax.c om:

>On 06 Aug 2007 00:17:30 GMT, Bob Quintal
<rq******@sPAm patico.cawrote:

>>>Try not to view it.... how can one open a query directly to
SQL mode, instead of design view?

If you save the query while in SQL view the query will open at
the SQL view when design is selected from the database window,
not the "standard" design view.
Wayne Gillespie
Gosford NSW Australia

I wish it would...

It does. I guess you are forgetting to save explicitly

Arno R

[David W. Fenton]

Bob Quintal <rq******@sPAmp atico.cawrote in
news:Xn******** **************@ 66.150.105.47:

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in
news:Xn******** *************** ***********@127 .0.0.1:

>Bob Quintal <rq******@sPAmp atico.cawrote in
news:Xn******* *************** @66.150.105.47:

>>The where clause approach is still the only one supported by
some major databases, and the Access query builder does not
allow a join on a.field < b.field.

Sure it does. You just have to edit it in SQL view and then
not try to view it with the query designer.

Try not to view it.... how can one open a query directly to SQL
mode, instead of design view?

The query designer saves the view. If you save it in SQL view, it
will open next in SQL view. In the case of a non-equi-join, the
query designer will never let you switch to design view (just as
with UNION queries).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/