pls help with query grid

[subs]

while creating a query using a grid, what is purpose of using
"where" . This comes up when we use Totals.
can we use a criteria just below the field which has "where" . Does
it work? pls help

eg of a grid

ocity dcity paid amt
weight paid date

groupby groupby sum
sum where

 

[Duane Hookom]

Check the SQL view if you have a modest understanding of SQL. The "Where" is
used strictly for criteria only and isn't part of the SELECT clause.

 

[John Spencer]

Where in an aggregate query allows you apply criteria to the record
selection BEFORE the data is aggregated (combined).

Say you want the sum of all amounts between two dates. If you use WHERE
Field: DatePaid
Total: Where
Criteria: Between #1/1/2008# and #1/31/2008#

You will get the sum for the entire period

On the other hand if you use
Field: DatePaid
Total: GROUP BY
Criteria: Between #1/1/2008# and #1/31/2008#

In this instance, the criteria is applied AFTER the records have been
aggregated and since you have grouped by the date that means there will
be one row for each date in the table and then the criteria will be applied.

You will get the Sums for each date in the range (up to 31 sums)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================