Average, Count, etc of entire column of table in SQL Query or as Function in VBA to Display result in Form?

Question

I need to calculate Count, Average and a couple other things of a column as a result of a Query and then put the result in a text box in a form.

I was thinking about either implementing it in the Query and putting the results in a column or something like that, OR use a VBA function to calculate it; however I don't know how to calculate this for an entire column in VBA.

Any suggestions/examples?

Answer 1

Have you considered the domain aggregate functions? These roughly take the form:

DAvg("SomeField","SomeTable","Where Statement, If Required")
DCount("*","SomeTable")

You can set the Control Soirce of a control to a function, but domain aggregate functions may not suit with a large recordset.

An alternative is to use a recordset:

 Dim rs As DAO.Recordset
 Set rs = CurrentDB.OpenRecordset("SELECT Count(*) As CountAll " _
        & "FROM SomeTable")

 Me!txtTextBox=rs!CountAll

Answer 2

You should do this operation in a SQL query. Aggregate operations such as this are what SQL excels at. It will give you the best performance and be the simplest solution.

Answer 3

Actually I found that this is very easy in VBA. I didn't want to add another field to my Query so I did this:

Forms!MyForm!AvgTextBox = Avg([mytable.values2Baveraged])
Forms!MyForm!CountTextBox = Count([mytable.values2Bcounted])

This calculates the function on the entire column. This worked perfectly just in case anyone cared.