PLEASE fill me in on this query design view behavior!

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

I am still relatively new to db programing and sql etc... To date I have
actually written all my queries in 'sql view' in access2002 because I really
want to understand the language. However I recently had to to something
quick and since I was under the impression the 'design view' would
facilitate this I gave it a shot:

well yada yada yada.. I wound up doing part in sql view anyway becuase it
was clearer to me when I can put my own parenthesis in and the like but I
encountered this behavior: After editing in sql view.. in particular adding
'or' comparisons... and switching back to design view access automatically
added each 'and' criteria for each field agian on the 'or' line of each
respective field in the design grid? query results stayed the same but I
thought this was a mistake and then I manually removed the redundant
expressions from the 'or' line of the grid. After that the query results
were totally different and upon viewing in sql view i saw that access
butchered my sql. I then put the redundant expressions back into the query
grid on the 'or' line and all was well agian??

What is this about?? I am totally confused and my head has almost exploded?

anyone?
 
As you discovered the "redundancy" is by design, which makes sense once you
think about it.
Each Criteria/Or *line* is treated as separate criteria with no knowledge of
any other criteria line. Example:

Fields selected: Year, Month, Amount

Query1:
Criteria: Year = 2003 AND Month = January
Or: Year = 2003 AND Month = March
Result: Records from January 2003 or March 2003

Query2:
Criteria: Year = 2003 AND Month = January
Or: Month = March
Result: Records from January 2003 or March *of any year*

Depending on your needs, either result set might be valid. Access provides
an interface that is flexible enough to give the user either or both with a
single, simple change. In order to to do that, it makes no "carry over"
assumptions about any field in an individual criteria line. This forces the
use of what may seem like the "redundancy" of 'Year = 2003' twice in Query1.
But how else would the interface be able to support query 2 when you wanted
it to?

I know this is a lousy example, but I hope it helps,
 
Back
Top