Use of "Where" in Query Total line

[TravelingHT]

I have a Trips form and a Visit sub form, the Visit sub form has a sub form
called “Trips†in the trips form.
In the Sub Sub Form “Visits†I want to filter the pets to only show the pets
from the customer selected in the primary form.
Should I be putting “Where†in the “Total:†line. When I put my instructions
in the criteria line for the Customer ID. This is the instructions I have:
[FORMS]![frmTrips]![Form]![CustomerID]
What is the significance of the "Where" statement in the "Total:" Line of
the colum?

Thanks in advance

TravelingHT

 

[Michel Walsh]

WHERE means that your criteria will be applied before the grouping and
aggregations occur. As example

Field: Amount Amount
Total: WHERE SUM
Criteria: >0 <100


will (logically) remove all records with their amount <= 0, keeping only
those with Amount > 0 (as per the WHERE ), then SUM the Amount value of the
records left, then, keep only the groups with that sum being less than 100
(as per the criteria under SUM). The SQL statement will look like:

SELECT ...
FROM ...
WHERE Amount > 0
GROUP BY ...
HAVING SUM(Amount) < 100




Vanderghast, Access MVP

 

[TravelingHT]

Dear Michel:

Thanks for your response.

In my example (please help me with the correct naming convention) when I
have a line of "Code" in the Criteria line:
[FORMS]![frmTrips]![Form]![CustomerID]

How will having "Where" and not having "Where" in the "Total:" line of the
colum affect the process/results of this query?

Yours truly,

Traveling HT

 

[Michel Walsh]

If you use where, you cannot use an aggregate neither a GROUP on this
column, although you can bring the field, from the table, a second time, in
another column, under which you will be able to then use an aggregate or a
GROUP.

If you use a WHERE clause, it removes the records which do not satisfy the
criteria, as it does for any other query.

If you have a total query and do not use either WHERE either GROUP either an
aggregate (SUM, MAX, MIN, COUNT... ), your only option is only to use
EXPRESSION but then, any involved fields in the first line, for that
expression, should be either in a group, either aggregated.


In short, if you GROUP BY cities and have a WHERE clause such as Continent =
North America, then, you will not have any group for London-UK, and
Paris-France, etc. If you remove that where condition, then you will get
group for London and Paris as well. But the where clause can also influence
the aggregates, such as removing 'values' which are too low or too high,
before SUMming them.


Vanderghast, Access MVP.