Find average of a field in a query

[SeadnaS]

How do i find average of a field in a query in Access VBA?

I have a query called "Q_XBAR" and I want to find the average of the field "RANGE

 

[kjv1611]

Easiest way?

1. Build the query in Design view.
2. In Query Design View, click the big E for Sum, etc..
3. In the query, change GROUP BY to AVERAGE on your column you want averaged.
4. Rename the field as you see fit by adding the NewName: in front of the field's name.
5. Make sure the query works by viewing in Datasheet view
6. Go to SQL view, copy the code.
7. in the VB editor, do this:

Private Sub MyButton_Click()
  Dim strSQL As String
  strSQL = "My SQL Code from the Query"
  DoCmd.SetWarnings FALSE
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings TRUE
End Sub

You'll only need the SetWarnings lines if it's an action query. But it looks like you just want a select query, so you can ignore those.

Give it a go, and post back with your results and further questions..

 

[SeadnaS]

Its ok managed to get it done before your reply! Thanks anyway!

 

[PHV]

What about the DAvg domain aggregate function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886