Finding the average in a table

N

Nada

Hi,
I am a novice to Access.
In a query or form, I want to show instantly the average of the marks
entered. Is it possible?
Thanks
Nada
 
A

Allan Murphy

Use the AVG function in your query as an example
In your query add the following column avg_marks:avg(marks) where marks is
the marks entered
 
S

Steve Schapel

Nada,

Yes, it is possible, via both a query and a form. In both cases, you
would use the Avg() function. Assuming the field where you are entering
the marks is named Marks, in design view of a query you would make a
calculated field in the Field row of the query design grid, something
like this...
AverageMark: Avg([Marks])
If you needed to get the average marks per person, or per week, or
whatever, then you would do this within a Totals Query, as applicable.

In a form, assuming a continuous view form, you would put an unbound
textbox in the Form Footer section, and in the Control Source property
of the textbox, enter the equivalent of...
=Avg([Marks])
 
J

Jeff Boyce

And I'll add one more chime-in...

If you have designed a well-normalized data structure, the AVG() function
(or DAVG(), depending on where you are doing this) will return the average
of a COLUMN of values.

If your data structure is more like a spreadsheet, using one column/cell per
value, Access does not have a function that sums, averages, etc. ACROSS
columns.

Is your data a "spreadsheet"?
 
N

Nada

Thanks Allan and Steve for yor suggestion.
My problem is that I actually have a table with grades for every student on
different topics, so I am not finding the average of a one column, but part
of a row for every student.
 
A

Allan Murphy

Nada

Add another column and depending on how many subjects do the following

Student_avg:([subject1]+[subject2] .... [subjectx])/no of subjects

What the above does for each student is add all the subject marks then
divide the total marks by the total number of subjects

If the students do not do all the same subjects then a bit of coding will be
required.
 
N

Nada

Yes, it is ;)


Jeff Boyce said:
And I'll add one more chime-in...

If you have designed a well-normalized data structure, the AVG() function
(or DAVG(), depending on where you are doing this) will return the average
of a COLUMN of values.

If your data structure is more like a spreadsheet, using one column/cell per
value, Access does not have a function that sums, averages, etc. ACROSS
columns.

Is your data a "spreadsheet"?

--
Regards

Jeff Boyce
<Office/Access MVP>
Click to expand...
 
S

Steve Schapel

Nada,

While Allan's suggestion is an interesting workaround, I have a question
for you... Is your table structure "set in concrete", or could you
consider a review of your design? You are using a database, and the
ideal solution would be to set it up according to database principles.
I imagine the effort to do so would be well worth it in the end.
 
J

Jeff Boyce

Please see Steve's suggestion.

Access makes a fine relational database, but a poor 'spreadsheet'. Both you
and Access will have to work a lot harder if you are trying to make it do
spread-sheetly things.

Any reason you couldn't export your data to a spreadsheet for these
calculations?
 
Top