Access Query Builder drives me nuts

[MikeB]

I'm designing a query using the Design View in Access since I find it
easier than typing the SQL (and making typing errors in table names,
etc.).

I just can't get what I want, so eventually I resort to hand-coding
the WHERE clause in SQL as follows:

WHERE (
(Tournaments.TournamentNumber=[Tournament?])
OR ([Tournament?] Is Null)
)
AND (
(Players.Status=[Status?])
OR ([Status?] Is Null)
)

This gives me exactly what I need.

But when I look at the design for the query, *this* is what I get. I
would *never* have figured out that that was how I had to set the
criteria. http://i36.tinypic.com/1g4r2q.jpg

However, if I make any modifications to the query in design view,
Access rewrites the WHERE clause as follows:

WHERE (((Tournaments.TournamentNumber)=[Tournament?]) AND
((Players.Status)=[Status?])) OR (((Players.Status)=[Status?]) AND
(([Tournament?]) Is Null)) OR (((Tournaments.TournamentNumber)=
[Tournament?]) AND (([Status?]) Is Null)) OR ((([Tournament?]) Is
Null) AND (([Status?]) Is Null))

Just for kicks, I "structured" this to see what it looks like and this
is what I came up with:

WHERE
(
(
(Tournaments.TournamentNumber)=[Tournament?]
)
AND
(
(Players.Status)=[Status?]
)
)
OR
(
(
(Players.Status)=[Status?]
)
AND
(
([Tournament?]) Is Null
)
)
OR
(
(
(Tournaments.TournamentNumber)=[Tournament?]
)
AND
(
([Status?]) Is Null
)
)
OR
(
(
([Tournament?]) Is Null
)
AND
(
([Status?]) Is Null
)
)

Can anyone shed light on this? I'm completely stupid here.

 

[BruceM]

The query builder just loves parentheses, for some reason. There are
usually several more pairs than are needed. My guess is that in some
situations the parentheses are needed, so they are added in all situations
since the extra ones don't do any harm, and it was too difficult to identify
automatically that a particular situation does not require them. It makes
for tough reading sometimes, though.

 

[MikeB]

The query builder just loves parentheses, for some reason. There are
usually several more pairs than are needed. My guess is that in some
situations the parentheses are needed, so they are added in all situations
since the extra ones don't do any harm, and it was too difficult to identify
automatically that a particular situation does not require them. It makes
for tough reading sometimes, though.

It's not so much the parenthesis that I'm complaining about, but the
query.

My query contained two parts joined by an AND clause. Each of the
parts had two conditions joined by an OR. Total of 4 conditions

The Query designer chose to rewrite that into three parts jouned by OR
clauses, each part containing two conditions joined by an AND. Total
of 8 conditions.

I'm trying to understand the difference in those constructs.

While it doesn't matter in small projects like mine, I'm curious about
the ultimate effect on performance and efficiency.

 

[david]

Yes, the query builder may do that. Normally it makes no difference,
but sometimes it does.

The two forms of the expression have identical meaning, so
they should (barring some obscure bug) return the same records.

Access runs a compiled version of the query anyway, so it
shouldn't matter what the text looks like.

However, the query optimiser starts with the text, and sometimes
changing the text can cause the optimiser to go down a different
path and settle on a different plan -- doing the join before the
WHERE, or doing one join before another, or whatever.

Sometimes you will find that if you run your SQL directly from
a querydef, or save the SQL to a querydef and never give the
design environment a chance to re-arrange it, you get a different
query plan.

But normally you don't know which form is better, and normally
it doesn't matter anyway.

(david)