Calculated Query Field averaging several fields in same record 1

[tbac]

Thought this would be simple but cant figure it out: I have a table with fields that represent each year with a number (usually) in each cell. Fields: 2003, 2004, 2005, 2006. I wanted to create a calculated query field that would average those four cells for each record. So I created the field 'Av':
AvAvg([2003],[2004],[2005],[2006])
I tried all kinds of variations, using semicolons, etc but it will not average the numbers for the different years. What am I doing wrong?

 

[PHV]

Av[2003]+[2004]+[2005]+[2006])/4

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

 

[fneily]

Fields: 2003, 2004, 2005, 2006."

This is not correct table design. It violates the first normal form. You have repeating column headings - year, year, year, etc. What you have is a category as field headings. You should only have one field called Year. What are you going to do for 2007, 2008, etc. Add new fields to the table? Wrong.

You'll run into more problems with an non-normalized table.
See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[tbac]

Fneily, I do not like having the data as it is but there are so many instances when I want to see the data side-by-side in a continuous form. With a normalized table the only way I can see the data side-by-side, is by using a subform wihin a single form. Is there another way?

Also, with respect to the Average, it seems like you could use a function instead of calculating it, because if there is missing data, you need a function

 

[dhookom]

Normalize the data and then use a crosstab or similar query to display the "year" columns.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

In a standard code module create the following function:

'A generic function to get the average value of an arbirtrary numbers of numeric values:
Public Function myAvg(ParamArray Args())
Dim i As Long, N As Long, rv
For i = 0 To UBound(Args)
  If IsNumeric(Args(i)) Then rv = rv + Args(i): N = N + 1
Next
If N > 0 Then myAvg = rv / N
End Function

And now in the query grid:
Av: myAvg([2003],[2004],[2005],[2006])

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

 

[tbac]

PHV;
Fantastic! Worked like a charm