average queries again

[fordy]

Does anyone know if there is an average if function. i.e show me the average of this field if it is greater than 5 for example.The field is a in this case

Ive tried this but it doesnt work

=IIf([a]>5,Avg([a]))

The field may have 20 records in it for example - life expectancy of roofs amd the cost of repair

10 smith road - 10 years £10
11 smith road - 20 years £25
12 alex street - 5 years £30

I want it to tell me the average cost of roof repairs for all roofs within the next 10 years. In other words discard 11 smith road.

[This message has been edited by fordy (edited 03-12-2002).]

 

[fordy]

Ive tried this and it doesnt work
I need it to show me the average cost of roofs that have less than 5 years to live

>(SELECT AVG([cost]) FROM [Table 2]) WHERE([life remaining]>5)

[This message has been edited by fordy (edited 03-12-2002).]

 

[fordy]

Im still stuck

Its really simple, I need the average cost for all records over 5. But cant seem to do it.

E.g

10
5
10
5
10

Would give me average of 5, sorry for my inadequacy

 

[Rich]

You say greater than 5 but include 5?
"I want it to tell me the average cost of roof repairs for all roofs within the next 10 years. In other words discard 11 smith road."?
"Its really simple, I need the average cost for all records over 5. But cant seem to do it."
Which one do you want greater than 5 but less than 20?

Between 5 and 19?

I think
SELECT Avg(A.Cost) AS AvgOfCost
FROM YourTable AS A
Where A.RoofAge >=5 And <=10;
is what you are really looking for?

[This message has been edited by Rich (edited 03-13-2002).]

 

[fordy]

Thanks it finally worked

however I want to do it several times, can this be done

and be put on the same report

 

[fordy]

Thanks it finally worked

however I want to do it several times, can this be done

and be put on the same report