Average certain records?

[Jay]

Hi,

I have a select query where records are grouped according to Car
Manufacturer. I want the query to also Average the 'Mileage' field, but
only for records where the Mileage is Between 45,000 and 75,000.

At present I have an 'Avg' in the Total field and 'Between 45000 And
75000' in the criteria row, but this means it averages all records and
selects results where the overall average is between the parameters.

I want it to only use records with mileages between those parameters in
the actual Average calculation.

I know I could probably do it with two queries, but if someone could
tell me how to do it in one I'd really appreciate it.

Many thanks,

Jay

 

[John Spencer]

Add a new column with Mileage as the field
Change Group By to Where
Insert your criteria

Or perhaps you mean

Field: Avg(Mileage Between 45000 and 75000,Mileage,Null) as AvgMiles
Change Group By to Expression

 

[Jay]

Cheers John,

You'll have to bear with me but I can't seem to get it to work. I'm trying
the 2nd suggestion.

I typed in exactly as you suggested but changed 'Field' to 'AverageMileage'
(assuming you mean the calculated fieldname). However, I get the following
error msg:

"The expression you entered has a function containing the wrong number of
arguments."

Any idea what I'm doing wrong?

Thanks,

Jason

Any idea

 

[John Spencer]

Your error? Copying my error.

Using the grid
Field: AverageMileage: Avg(Mileage Between 45000 and 75000,Mileage,Null)


In SQL
Select FieldA,
Avg(Mileage Between 45000 and 75000,Mileage,Null) as AverageMileage
FROM YourTable
GROUP By FieldA

 

[Jay]

Thanks John, but I'm still getting an error.

Would you mind doing me an absolute idiot's guide. Let's say I have two
fields: Model & Mileage.

I want to group by Model & calculate an average mileage, but only including
mileages from 45000 to 75000.

I've tried group by Model and your suggestions using design grid for the
Average Mileage but still get a 'wrong no. of arguments' error. Am I right
in thinking that that needs "Expression" in the Totals field?

Thanks for your patience,

Regards

Jason

 

[John Spencer]

Ok, let's try this.

--Open a new query
--Add in your table
--Add the Model field and the mileage field to the query
--Add the mileage field AGAIN
--Select View: Totals from menu
--Change "Group By" to "Average" under the first mileage
--Change "Group By" to "WHERE" under the second mileage
--Insert Between 45000 and 75000 as criteria under the where
--Run the query

Does that work? If so, what do you need to do next?