Querying mean values without using "Totals"

[Guest]

Dear Access Community

I'm a novice regarding Access and have an apperently simple problem.
I want to query the average of a numeric field, without using the "Totals"
approach.
Using the expression builder with the function "Avg" in the cell "criteria"
causes following error message (I translated the message from portuguese into
english, so the original message is probably a little different: "It is not
possible to have a aggregated function in a WHERE instance".

Can anybody help me ?

Thanks a lot !

gabriel

 

[Guest]

Your question is a bit difficult to understand. Could you provide a
significant amount of records and the expected display?

Why don't you want to use totals? Why would you want to use a criteria?

 

[Guest]

Why not use Totals?

You could try the DAvg Function.

 

[Michel Walsh]

You cannot use an aggregate in a WHERE clause. Try using it in an HAVING
clause. The WHERE clause is executed BEFORE any aggregation, so, the result
of the aggregation cannot be tested in it, at that stage.

SELECT id, AVG(something)
FROM somewhere
GROUP BY id
HAVING AVG(something) >= (SELECT AVG(something) FROM somewhere)


will return the groups 'id' where their average is larger than the average
of the whole table.



Vanderghast, Access MVP

 

[Guest]

Dear Mrs. Hookom

Thank you for your answer!

In reply for your request, I give you a little description of the intended
database design and a screenshot (to your email).

I do desktop mapping and would like to maintain my maps by Access through
dynamic linking.

To link the access database to my vector polygons in the map (sugar cane
fields), I need the key field "ID".


The second field refers to different sugar cane species, but I can't use
"totals", because I have to preserve all records

in the field to match the "ID" field to be able to link the records to the
corresponding polygons.

And as I understood, you can't do a query with one field using "totals"and
the other not (or is it possible?)

The third field is the average sugar quantity for each sugar cane species.

As I can't do "totals", I tried the average function approach with WHERE
clause through the expression builder in the criteria cell.


How can I create a query as seen on the screenshot?

Thanks again for your valuable help!

gábor

"Duane Hookom" escreveu:

 

[Guest]

Thank you for your answer.
I tried DAvg and there wasn't any error message anymore, but the query
returned an empty field

"Jerry Whittle" escreveu:

 

[Guest]

Hi!

Thank you for your answer!

I couldn't write the expression, there was always a syntax error message.


"Michel Walsh" escreveu: