Difference between Criteria field and Where field?

[NewShoes]

Hey all,

I'm just looking at the QBE window in Access 2007 and noticed that there is Criteria which is where I would normally put things like >100. However, if you activate the Totals row, you can use the drop down menu to select "Where".

Can anyone tell me the difference between Criteria field and Where field as it seems they are doing the same thing!

Thanks,
-NS

 

[Brianwarnock]

I assume that you mean Access not Excel.

You have already noticed that criteria can be applied without it being an aggregate query.

If you want to apply that criteria in an aggregate query and do no aggregate function such as Group By, then you must select the Where option, the show button will be deselected.

I am assuming that 2007 is the same as earlier releases on this.

Brian

 

[NewShoes]

Ooops yeah, Access to Excel!

Thanks for the answer Brian. I understand most of what you said but is there anyway you could put it more in laymans terms?
-NS

 

[Brianwarnock]

The where option on the Totals row is to tell the Totals (aggregate) query to use the information in the criteria row in its Where clause, so you can consider that the Where is an aggregate function just like all the others from the drop down, Max, average etc and in a Totals query all columns must have an aggregate function applied.

As mentioned my the first post the criteria can be in a column with another aggregate function applied, this then appears in a Having clause, the difference is that a Having clause is applied after aggregation whereas a Where clause is before.

Consider a field MeterReading which you are going to sum and you are grouping on MeterID, if you put the criteria in the same column as the Sum you select only where the Sum meets the criteria, if you create another column for MeterReading and select Where and place the criteria in that column you select only where each reading meets the criteria.


Brian

 

[NewShoes]

Thanks Brian, that helps a lot