where in selection query?

[sybmathics]

I am really puzzled about the "where" option in a totals query.

To get data for a specified year i'd normally use Myyear:year([datefield])
and add a criterium for the needed year.

I've seen another user use the "where" statement replacing the "group by"
row.
Where can I find information about this "where"-thing.


Sybolt

--
||//////||
( o o )
( O )
-
( )
( )
(______O______)

 

[Allen Browne]

A Total query has a GROUP BY clause, which defines how the data is
aggregated. It may or may not have a WHERE clause as well.

If it has a WHERE clause, Access applies that before selecting the records
and grouping them. For example, if you only want the records for a date
period, it would be most efficient to use a WHERE clause so JET does not
have to add them all up and then discard the other periods.

If the Total query has a HAVING clause, this is applied after aggregation.
It makes sense to use this where you want to apply criteria on the
aggregarted total. For example if you want the query to return only those
clients that had 10 or more invoices in the period, you would use the WHERE
clause to exclude invoices outside that period, and then the HAVING clause
on the count of invoices (which is only available after JET has aggregated
them.)

In query design view, you are applying a HAVING clause when you put criteria
under a field that has GROUP BY in the Total row. If you want to use the
WHERE clause, you must choose WHERE in the Total row of the query.
(Sometimes that means the field actually turns up twice in the query design
grid: once with WHERE so you can exclude records, and again with GROUP BY or
some other form of aggregation.)

 

[sybmathics]

Thanks very much.