Just so I understand the Query Grid...

[Jack MacDonald]

OK, I feel like I have some experience w/ Access and developing simple
Access apps, but one thing that has consistently eluded me is the whole
"Grid vs. SQL" thing. I realize that SQL is the way to go in terms of
understanding queries, being able to write better code, etc., but my
learning curve is impeded by the deadlines I'm always given. In my spare
time, I do a lot of making queries and then looking at SQL view to see the
"real deal," but it hasn't helped a whole lot as of yet.

So, I'm looking for a few pointers:

When adding "OR" rows, do I need to duplicate all the criteria from the
above "And" rows? For instance, for a query with fieldnames OrderId and
OrderDate and ShipDate, if I have in the criteria under "OrderDate"
"<=#1/2/05#" and under the ShipDate "Date()+2", but I also want to see
orders with the same OrderDate but including ShipDates of Null, do I need to
have in the "Or" row:

"<=#1/2/05#" (under OrderDate) and also "Is Null" under ShipDate?

Depends how you construct the SQL -- these two Where clauses are
equivalent:

Version 1
WHERE
(OrderDate <=#1/2/05# AND ShipDate = Date() + 2)
OR
(OrderDate <=#1/2/05# AND IsNull(ShipDate))

Version 2
WHERE
(OrderDate <=#1/2/05# )
AND
(ShipDate = Date() + 2 OR IsNull(shipDate))

The placement of the parentheses is crucial. The Access QBE will
always write an SQL statement like version 1. As a programmer, you can
optionally write version 2 -- you may find it easier to read.

On the other hand, this is a completely different Where clause
WHERE
(OrderDate <=#1/2/05# )
AND
(ShipDate = Date() + 2)
OR
IsNull(shipDate)

because it will return all records with null ShipDate, regardless of
the OrderDate. Do NOT write this version because it is probably not
what you intended.

Think back to how you learned the precedence rules for multiplication
and addition. You must apply similar "precedence" rules when doing
ANDs and ORs. When in doubt -- use parentheses to force the order of
precedence.

See, my impression was that the "Criteria" row carried down into the "Or"
row unless specified otherwise, but I get mixed results trying both methods.
I've read Access' Help on this, but often when I get help on the NG's about
how to set up queries using SQL (which everyone seems to use by default,
what with not being newbies and all), the Query grid shows all these extra
columns with the Criteria Rows being repeated, but still in separate
columns, rather than using the "Criteria" and multiple "Or" rows underneath
one column.

See, often I get SQL that when viewd in Query Design seemed to defy even my
questioning of the "repeating" things from the criteria row: a lot of the
time I see additional columns, not just things added to the existing colums,
with things in the "Criteria" row AND in the "Or" row.

Does someone have a nice, succinct explanation of how to use multiple "Or"
criteria, or leveled "And" and "or" criteria using the Grid? Do any other
newbies out there know what I'm talking about, and have found help in this
area? I'd rather grasp the concept than posting every time I have a complex
query problem, but going from the SQL view of newsgroup posts to the Grid
always seem to defy what I thought was an intuitive interface.

Any "rules of thumb" or nice "not intuitive, but here's the basic approach"
advice is greatly appreciated.

Sorry for being so basic, but the posts I've seen are specific to particular
problems, and Access' Help on query building didn't really educate me in
this particular regard.

Thanks in advance.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[rgrantz]

OK, I feel like I have some experience w/ Access and developing simple
Access apps, but one thing that has consistently eluded me is the whole
"Grid vs. SQL" thing. I realize that SQL is the way to go in terms of
understanding queries, being able to write better code, etc., but my
learning curve is impeded by the deadlines I'm always given. In my spare
time, I do a lot of making queries and then looking at SQL view to see the
"real deal," but it hasn't helped a whole lot as of yet.

So, I'm looking for a few pointers:

When adding "OR" rows, do I need to duplicate all the criteria from the
above "And" rows? For instance, for a query with fieldnames OrderId and
OrderDate and ShipDate, if I have in the criteria under "OrderDate"
"<=#1/2/05#" and under the ShipDate "Date()+2", but I also want to see
orders with the same OrderDate but including ShipDates of Null, do I need to
have in the "Or" row:

"<=#1/2/05#" (under OrderDate) and also "Is Null" under ShipDate?

See, my impression was that the "Criteria" row carried down into the "Or"
row unless specified otherwise, but I get mixed results trying both methods.
I've read Access' Help on this, but often when I get help on the NG's about
how to set up queries using SQL (which everyone seems to use by default,
what with not being newbies and all), the Query grid shows all these extra
columns with the Criteria Rows being repeated, but still in separate
columns, rather than using the "Criteria" and multiple "Or" rows underneath
one column.

See, often I get SQL that when viewd in Query Design seemed to defy even my
questioning of the "repeating" things from the criteria row: a lot of the
time I see additional columns, not just things added to the existing colums,
with things in the "Criteria" row AND in the "Or" row.

Does someone have a nice, succinct explanation of how to use multiple "Or"
criteria, or leveled "And" and "or" criteria using the Grid? Do any other
newbies out there know what I'm talking about, and have found help in this
area? I'd rather grasp the concept than posting every time I have a complex
query problem, but going from the SQL view of newsgroup posts to the Grid
always seem to defy what I thought was an intuitive interface.

Any "rules of thumb" or nice "not intuitive, but here's the basic approach"
advice is greatly appreciated.

Sorry for being so basic, but the posts I've seen are specific to particular
problems, and Access' Help on query building didn't really educate me in
this particular regard.

Thanks in advance.