Warning. Min/Max use on fields with criteria

[Salad]

This may occur to you in the future. You want to get the min or max of
a field that you set criteria on...maybe for a combo box. Let's say you
wanted to get the minimum of all date fields where the date field is in
the future. If you use the query builder and don't modify the results
you end up with zero records. Ex.

SELECT Min(DateField) AS MinDate
FROM Table
HAVING Min(DateField) > Date()+1

The above is code generated, minus all the ()'s, from the query builder.

If you really have the min or max records beyond today, you need to
manually modify the query to change the word Having to Where

SELECT Min(DateField) AS MinDate
FROM Table
Where DateField > Date() + 1

[Tom van Stiphout]

Re: Warning. Min/Max use on fields with criteria

On Sat, 10 Apr 2004 20:33:59 GMT, Salad <oil@vinegar.co m> wrote:

You can achieve the same by adding another DateField column in the
query designer, rather than adding the criteria to the first DateField
field.

-Tom.

[color=blue]
>This may occur to you in the future. You want to get the min or max of
>a field that you set criteria on...maybe for a combo box. Let's say you
>wanted to get the minimum of all date fields where the date field is in
>the future. If you use the query builder and don't modify the results
>you end up with zero records. Ex.
>
> SELECT Min(DateField) AS MinDate
> FROM Table
> HAVING Min(DateField) > Date()+1
>
>The above is code generated, minus all the ()'s, from the query builder.
>
>If you really have the min or max records beyond today, you need to
>manually modify the query to change the word Having to Where
>
> SELECT Min(DateField) AS MinDate
> FROM Table
> Where DateField > Date() + 1
>[/color]

[Matthew Sullivan]

Re: Warning. Min/Max use on fields with criteria

A little additional info that might help someone out there:

In a query that has no aggregation (GROUP BY, MIN, MAX, etc), the
criteria in the WHERE clause are applied to the records at the time of
selection.

But in a query *with* aggregation, there are two options for how you
apply criteria: at the time of selection (WHERE clause) or after
grouping (HAVING clause).

As in Salad's example, the incorrect application of criteria (WHERE vs
HAVING) can have an effect on the result set of the query.

In other cases, the result set could be the same, but the
performance can be significantly affected. For instance, if you want
the Max of some value on a specific date, applying the date criterion
in the WHERE clause will weed out all but the date in which you're
interested prior to the application of the Max function. But if you
apply the date criterion in the HAVING clause, your query will select
*all* dates and calculate the Max value for each date, before
applying the date criterion and showing you only the date you're
interested in. (Unless Access/Jet has some kind of SQL optimization
built in of which I'm not aware.)

This is, IMHO, one of the places in Access where the GUI is a little
too user-friendly, and hides something significant from the user.

-Matt



On Sat, 10 Apr 2004 20:33:59 GMT, Salad <oil@vinegar.co m> wrote:
[color=blue]
>If you really have the min or max records beyond today, you need to
>manually modify the query to change the word Having to Where[/color]

[Lyle Fairfield]

Re: Warning. Min/Max use on fields with criteria

Matthew Sullivan <Matt@NoSpam.co m> wrote in
news:2eej70pe93 ms2rvpekuhc3g4n ovin87olp@4ax.c om:
[color=blue]
> A little additional info that might help someone out there:
>
> In a query that has no aggregation (GROUP BY, MIN, MAX, etc), the
> criteria in the WHERE clause are applied to the records at the time of
> selection.
>
> But in a query *with* aggregation, there are two options for how you
> apply criteria: at the time of selection (WHERE clause) or after
> grouping (HAVING clause).
>
> As in Salad's example, the incorrect application of criteria (WHERE vs
> HAVING) can have an effect on the result set of the query.
>
> In other cases, the result set could be the same, but the
> performance can be significantly affected. For instance, if you want
> the Max of some value on a specific date, applying the date criterion
> in the WHERE clause will weed out all but the date in which you're
> interested prior to the application of the Max function. But if you
> apply the date criterion in the HAVING clause, your query will select
> *all* dates and calculate the Max value for each date, before
> applying the date criterion and showing you only the date you're
> interested in. (Unless Access/Jet has some kind of SQL optimization
> built in of which I'm not aware.)
>
> This is, IMHO, one of the places in Access where the GUI is a little
> too user-friendly, and hides something significant from the user.[/color]

The original poster specified two entirely separate and distinct criteria in
his cirterion clause.

HAVING Min(DateField) > Date()+1

and

Where DateField > Date() + 1

Of course "having" scans the aggregations in this case
(and "where" does not).

What is you reason for thinking
"having" will scan the aggregations in

HAVING DateField > Date()+1

?????

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Matthew Sullivan]

Re: Warning. Min/Max use on fields with criteria

Lyle:

I wasn't intending to say anything about that syntax in particular
(offhand, I don't even know whether it's valid).

My apologies if I wrote poorly.

When I was talking about applying the same criterion in either the
WHERE or HAVING and getting the same result set, I meant an example
something like this (note: SQL Server sytnax):

SELECT Max(NumberOfThi ngys) as MaxNumThingys, Date
FROM tbl_Thingys
WHERE Date = '4/1/2004'
GROUP BY Date

versus this:

SELECT Max(NumberOfThi ngys) as MaxNumThingys, Date
FROM tbl_Thingys
GROUP BY Date
HAVING Date = '4/1/2004'

-Matt



On 11 Apr 2004 22:40:04 GMT, Lyle Fairfield
<MissingAddress @Invalid.Com> wrote:
[color=blue]
>What is you reason for thinking
>"having" will scan the aggregations in
>
>HAVING DateField > Date()+1
>
>?????[/color]

[John Winterbottom]

Re: Warning. Min/Max use on fields with criteria

"Matthew Sullivan" <Matt@NoSpam.co m> wrote in message
news:2eej70pe93 ms2rvpekuhc3g4n ovin87olp@4ax.c om...[color=blue]
>
> This is, IMHO, one of the places in Access where the GUI is a little
> too user-friendly, and hides something significant from the user.
>[/color]

Saying these sorts of things round here is generally considered to be
promoting heresy <g>