[AccessD] This doesn't look right

There's a difference between WHERE and HAVING, as you discovered.  Using
WHERE in a totals query doesn't include the field being evaluated in the
output fields, it just filters the result based on that field.  HAVING
includes the field being evaluated, and that has a profound effect of
the results, since you're also grouping by those fields.

Charlotte Foust

-----Original Message-----
From: Steven W. Erbach [mailto:serbach at new.rr.com] 
Sent: Thursday, January 29, 2004 8:37 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] This doesn't look right


Dear Group,

I'm "re-factoring" an application that I took over from a developer
who's reducing her workload to devote more time to her family. There is
a query that looks curious to me and I wonder if you lot could give me
some insight into how Access works.

The query creates a SubTotal and has four Criteria rows and, of course,
the Total line is activated. The query looks at four fields on an open
form (frmReport) which feed values into the query. Two of those fields
are txtLogStartDate and txtLogEndDate. The other two are txtCity and
txtCounty.

The four Criteria rows look at various combinations of whether txtCity
and txtCounty are filled in or not on the form that feeds the query. The
ArrivalDate in the query grid looks at the range of dates between
txtLogStartDate and txtLogEndDate on the form.

What's odd is that, at first, the ArrivalDate query column had WHERE in
the Total line. The County and City columns show Group By. The range of
dates is supposed to be the same for every one of the four Criteria
rows...BUT with ArrivalDate set to WHERE there was only one Criteria
statement in the first row of the four Criteria rows. It looked like
this:

Between [Forms]![frmReport]![txtLogStartDate] And
[Forms]![frmReport]![txtLogEndDate] Or Between
[Forms]![frmReport]![txtLogStartDate] And
[Forms]![frmReport]![txtLogEndDate] Or Between
[Forms]![frmReport]![txtLogStartDate] And
[Forms]![frmReport]![txtLogEndDate] Or Between
[Forms]![frmReport]![txtLogStartDate] And
[Forms]![frmReport]![txtLogEndDate]

(Sorry for the wrap) That is, that entire thing was in one cell of one
row of the Criteria grid. I tried to cut and paste individual "Between"
statements into each of the four rows of the Criteria grid; but when I
saved the query, closed it, and re-opened it, that huge long
multiple-Between statement re-appeared in just the first row of the
Criteria grid.

However, when I changed the ArrivalDate Total line to GROUP BY and
*then* cut and paste individual Between statements into each row of the
Criteria grid, when I saved, closed, and re-opened the query the Between
statements stayed where they were; that is, one for each of the four
rows of the Criteria grid.

Now, it was obvious to me that the two forms of the query would produce
different results, and that turned out to be true. My question is this:
when you're putting together a multi-Criteria row query and you want one
column to show the same criteria in each row, the natural thing to do is
choose WHERE in the Total row. You can test the query while you're
designing it and you'll get results. But when you save the query,
thinking that you've done your duty, you're actually saving something
incorrect since, if you don't look at it again, Access will re-format
all those WHERE statements in each row of the Criteria grid into one
concatenated statement in one row.

I thought I knew queries pretty well, but this one throws me. In the SQL
statement formed when the ArrivalDate showed WHERE, that big long
concatenated multi-Between statement wound up in the WHERE clause.
(Didn't matter whether the Between statements were in separate criteria
rows or not.) But when I reformed the query to split the Between
statements into separate Criteria rows and selected GROUP BY for the
ArrivalDate column, the Between criteria wound up in the HAVING clause.

I guess it's just an experience thing, right?
 
Regards,
 
Steve Erbach
Scientific Marketing
Neenah, WI
920-969-0504

Message created with Bloomba
 
Disclaimer: No tree was killed in the transmission of this message.
However, several coulombs of electrons were temporarily inconvenienced.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com