why would you add where in selectio query

[imda14u]

Hi all,

in totalsquery you enter a criterium on a field for records to fit the
criterium.

For instance: i add 2007 as criterium for a field i created as follows:
theyear:year([invoicedate])
the result being that we only get the dates for 2007.
In the groupby rule you can choose "where".
In what circumstances would you use that option?

Thanks in advance,

Syb Ho

 

[John Spencer]

Where should be used when you want to limit the records you are
aggregating. You use criteria under a field that you are aggregating
when you want to limit the results by the aggregation.

Field: Cost
Total: SUM
Criteria: > 2000

Field: Cost
Total: Where
Criteria: > 2000

The first one will return results where the sum of the cost field is
more than 2000. The second one will return limit any records that are
to be combined to those where the individual cost is more than 2000.

So, if you wanted a count of records with a cost of more than 2000 you
would have

Field: Cost
Total: Count
Criteria: B L A N K

Field: Cost
Total: Where
Criteria: > 2000

There is a difference in performance between screening the records
before aggregation and after aggregation. So if you are applying
criteria to a column you are grouping by, you can often significantly
improve performance by adding a second column on that same field and
using WHERE to filter the records.

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

 

[Jeff Boyce]

If you calculated "theyear: Year([InvoiceDate]) but did NOT limit it to
2007, you'd get your results GroupedBy "theyear". After doing that, assume
you wanted to see only results for 2007 ... that could be one situation in
which you started with GroupBy, then added a Selection Criterion.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[imda14u]

Where should be used when you want to limit the records you are
aggregating. You use criteria under a field that you are aggregating
when you want to limit the results by the aggregation.

Field: Cost
Total: SUM
Criteria: > 2000

Field: Cost
Total: Where
Criteria: > 2000

The first one will return results where the sum of the cost field is
more than 2000. The second one will return limit any records that are
to be combined to those where the individual cost is more than 2000.

So, if you wanted a count of records with a cost of more than 2000 you
would have

Field: Cost
Total: Count
Criteria: B L A N K

Field: Cost
Total: Where
Criteria: > 2000

There is a difference in performance between screening the records
before aggregation and after aggregation. So if you are applying
criteria to a column you are grouping by, you can often significantly
improve performance by adding a second column on that same field and
using WHERE to filter the records.

O, I see.
Thanks for the quick response