Query to show AVERAGE of all values in a field

[taimysho0]

Hello, how do i create a query that will take all the values in a column and divide it by every row in the table? thanks!

[Robeen]

SELECT Avg(TableName.FieldName) AS Average
FROM TableName;

[orange]

Something like the following should work, or at least get you started

select SumOfAmount/CountOfId as Aver_age
FROM
(SELECT Sum(Grades.Amount) AS SumOfAmount, Count(Grades.id) AS CountOfid
FROM Grades);

In my sample SUM(Grades.Amount) represents the summing all values in a column.
Count(Grades.id) represents the number or rows in the table.

good luck

oops I see Robeen has responded while I was typing.

[Robeen]

sorry . . . Orange has a more accurate solution.

Mine will only be correct if every row in the table always has a value in the field you need to average.

[taimysho0]

hello, im not too familliar with the SQL view, can you guys help me with putting this in Design View where it shows the table and such?

for example, what do i put in the "field" box or "criteria" box?

currently, my query spits out a table. i just want one column to display the average of the values in the "days late" column in that table. thanks!

[taimysho0]

i tried the SQL view and this is what i have:

SELECT Sum(tblHHFRequest_Main.Days_Late) AS SumOfAmount, Count(tblHHFRequest_Main.Channel_ID) AS CountOfid
FROM tblHHFRequest_Main;


when i run it, a box appears saying "enter parameter value" for tblHHFRequest_Main.Days_Late

[Robeen]

The 'enter parameter value' message - if you didn't create a 'Parameter Query' has usually meant [for me] that I have had a table name or field name in my query that doesn't exactly match a table name or field name in my database.

The SQL that you posted doesn't do any Averaging. It does one 'Sum' and one 'Count'.

Have you tried the suggestion that I posted earlier?
Try it and see if it gives you what you need.
If it does not, then you can pursue what Orange suggested.

Let us know how you fare.

[taimysho0]

thank you, that worked.

also, i have a report that has a record source of a seperate query. The average query above will spit out only 1 value. Is it display the result of this query above onto my report who's record source if from another query? thanks!

[Robeen]

What you might be able to do for your report is this:
Create a third query that combines the data from the query that currently feeds the report, and your Average query:
In design mode select both queries as your data source.
Pull in all the fields from your report query and also the Average field from your average query.
Save the new query.
Open the report in design view and go to the Data tab and change the Record Source to your newly created query.
Then use the 'Add Existing Fields' to add your average field to the report.
Remember - there is only one Average value from your average query.

Hope this helps!

[taimysho0]

I see, thank you!