Averaging the data in a row with access

  • Thread starter Thread starter Michael Beatty
  • Start date Start date
M

Michael Beatty

Here is my query
Name Edison Oak Harbor Huron Home
Jim 43 44
42 43
Tim 45
45 49
Slim 44
47

I want to display the average of each person. It needs to be able to
handle the NULL values by not including them. This would be easy to add the
four fields and devide by four, however some of the values are NULL. This
should be simple, but I can't figure this out. This is for a Visual Basic
application. I'm the application programmer, the person who is in charge of
the data base and it's queries is no longer in the group and I need to fill
in his gap.
 
Dear Michael:

This is pretty simple, but I'll bet you won't like it.

Put the data in a table like this:

Name Place Value
Jim Edison 43
Jim Oak Harbor 44
Tim Oak Harbor 43
Tim Huron 45
Tim Home 49
Slim Oak Harbor 44
Slim Home 47

Then use the average aggregate function:

SELECT Name, Avg(Value)
FROM YourTable
GROUP BY Name

This has an advantage of not requiring you to create a new column
every time you add another Place, besides allowing you to easily do
just what you asked. Designing tables this way is a fundamental of
good table design, mostly because it works well. In fact, the tools
we use in Access, or any other database product, are built around the
ability to do this easily if you desing tables in this way.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
That is how I started doing it, however I have one problem. The oppoent
scores are in a different table than the home score. I hope I'm not bitting
off more than I can chew
 
Back
Top