Using DAvg (1 Viewer)

[grahamw]

Hi All,
I want to average an array cur_races(1 to 10)
DAvg(expr, domain, [criteria]) seems like a good choice but it
seems to imply that I have to identify a field, table or query etc.
How can I just get the average of an array? (without setting up loop).
This would be easy in excel but Im having difficulties here with access.
Can anyone help please.
Thanks
Graham

 

[MarkK]

Why avoid a loop? Loops are beautiful!

Public Function GetAvg (SomeArrayOfNumbers as Variant) as Single
  Dim i As Integer, j As Integer  [COLOR=Green]'indices[/COLOR]
  Dim sSum as Single

[COLOR=Green]  'traverse array elements - loop[/COLOR]
  For i = Lbound(SomeArrayOfNumbers) to Ubound(SomeArrayOfNumbers)
    [COLOR=Green]'count elements[/COLOR]
    j = j + 1
[COLOR=Green]    'sum elements[/COLOR]
    sSum = sSum + SomeArrayOfNumbers(i)
  Next i

[COLOR=Green]  'divide sum by count of elements, and assign to function[/COLOR]
  GetAvg = sum / j

End Function

 

[Pat Hartman]

This would be easy in excel but Im having difficulties here with access.

That's exactly what happens when you take a spreadsheet and call it a table. You have a 1-many relationship (the data you are trying to average) and have stored it as you would in a spreadsheet. The problem is, you want to use the data in Access. Take off your spreadsheet thinking cap and put on your relational database thinking cap. Do some reading on normalization if you need to.

Relational databases, of which Access is one, NEVER provide aggregate or domain functions that work across the columns in a row. Aggregates or domain functions only work on a domain - that means set of rows.

If you persist in making spreadsheets and calling them tables, you're in for a lot of disappointment with Access and a lot of VBA for yourself.

 

[grahamw]

I appreciate your advice Pat.
Ive just read a bit on normalization.
The database (of horse form I use) is produced by a company in the UK. The database is large and consists of 4 linked tables.
Attempts to use excel were thwarted due to the complexity of the tasks I need to perform.
However, maybe using excel on a subset of results from access will allow me to carry out the statistical tests.
Regards
Graham

 

[Pat Hartman]

You will be much happier overall if you normalize the data. Then you will have the full features of Access and queries at your disposal. As it is now, you are limited in what you can do.

Don't forget, Access can make pivot tables as well as Excel can and with more rows since it is not limited to the 64k that Excel is.