Query - Display only total amount higher than the average...

[goody444]

I am creating a query that I would like to have list all of the total amounts that are higher than the average of the list. I can do this by simply averaging all of the totals and then writing ">(amount of average)" into the criteria box, however I would like Access to calculate the average for me. How would I write the criteria for this?

Example:

1) 200
2) 300
4) 100
5) 200
6) 50
7) 150

Average: 143
Therefore only 1, 2, 5, and 7 would be displayed.

 

[Russell Hauf]

Try something like this (my table name is tbl041122 and my field name is FIELD1) -- in the criteria line of your field, put (replacing my names with yours):

>(SELECT Avg(FIELD1) FROM tbl041122)

 

[goody444]

Re: Query - Display only total amount higher than the averag

I tried your suggested criteria input, however I'm unsure of what my table name will be. I have created the totals field by creating a custom formula for the field (using sum(x,x,x)). How would I relate the criteria to this specific table? Sorry if this is hard to understand, I am a beginner at Access. Thanks for any help!

 

[Russell Hauf]

Re: Query - Display only total amount higher than the averag

Can you paste the SQL from your query (the one w/o trying to show only those above the average)? When in query design view, go to View-SQL.
-rh