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=subjects.taught_by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name=subjects.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=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.

[David W. Fenton]

"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM>
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.orgZERO_SP AMwrote:

>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******@sPAmpatico.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*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Bob Quintal <rq******@sPAmpatico.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******@sPAmpatico.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*****@NOhotmailSPAM.com.auwrote in
news:ah********************************@4ax.com:

On 06 Aug 2007 00:17:30 GMT, Bob Quintal
<rq******@sPAmpatico.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******@sPAmpatico.caschreef in bericht news:Xn**********************@66.150.105.47...

Wayne Gillespie <be*****@NOhotmailSPAM.com.auwrote in
news:ah********************************@4ax.com:

>On 06 Aug 2007 00:17:30 GMT, Bob Quintal
<rq******@sPAmpatico.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******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

>Bob Quintal <rq******@sPAmpatico.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/

[David W. Fenton]

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

Wayne Gillespie <be*****@NOhotmailSPAM.com.auwrote in
news:ah********************************@4ax.com:

>On 06 Aug 2007 00:17:30 GMT, Bob Quintal
<rq******@sPAmpatico.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.

I wish it would...

Are you saying that your version of Access does not do that?

Or that you can't do it with other kinds of queries?

I have some normal queries that work fine in design view that have
been saved in SQL view and that open in SQL view the next time I
design them. But I've never tested it systematically to see if I can
force the query to always open in SQL view.

Is that what you mean?

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

[Bob Quintal]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

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

>"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0 .1:

>>Bob Quintal <rq******@sPAmpatico.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).

I will assume that this behaviour is a change since Access 97,
because that version trashes the SQL if I try to open a query
saved in SQL mode, it opens in design mode, with a non-equi-join
SQL statement.

But it's good to know. Thanks for the info.

--
Bob Quintal

PA is y I've altered my email address.

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

[Rick Brandt]

Bob Quintal wrote:

I will assume that this behaviour is a change since Access 97,
because that version trashes the SQL if I try to open a query
saved in SQL mode, it opens in design mode, with a non-equi-join
SQL statement.

But it's good to know. Thanks for the info.

Hmm, I exclusively design in A97 and when I open a query with a non-equi-join in
design view I get a message that the join cannot be represented in the query
design window and then after acknowledging that message I get SQL view. I have
never experienced the query getting "trashed".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

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

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

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

>>"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0. 0.1:

Bob Quintal <rq******@sPAmpatico.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).

I will assume that this behaviour is a change since Access 97,
because that version trashes the SQL if I try to open a query
saved in SQL mode, it opens in design mode, with a non-equi-join
SQL statement.

Hmm. I did my testing in A97 first, and it worked exactly as I
describd it.

But it's good to know. Thanks for the info.

Most of my work is A97, and I do this kind of thing all the time and
have very seldom "trashed" my SQL (though it has happened, yes).

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

[David W. Fenton]

Arch <se*****@spam.netwrote in
news:ah********************************@4ax.com:

I made no suggestion that the two statements would be treated
identically by Jet.

But the *are* optimized the same by Jet, as you can find out by
turning on SHOWPLAN.

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