Average values in a row 1

[TrishMcK]

Hi,

I have a very simple table which holds assessment marks for a subject for each pupil i.e.

Pupil , Assess1, Assess 2, Assess 3
J Smith 30, 40, 30
J Jones 20, 30, 50 ....

I would like to create a new field called average and have the result of the average of the three assessments. I can do it with ([Assess 1]+[Assess 2]+[Assess 3])/3 but there must be a slicker way.

The only information I have been able to find looks and using SQL and VB which I have no experience of. I apologise in advance if this is too simple.

T M

 

[JerryKlmns]

Welcome TrishMcK,

Some (if not all) will argue about normalizing this table.

But to your current problem you don 't need another column, just a query to do the job

Create a new query, change to SQL view and paste the following.

Select Pupil, Assess1, Assess2, Assess3, (nz([Assess1],0)+nz([Assess2],0)+nz([Assess3],0))/3 AS AvgAssess
From [blue]Table[/blue];

Change [blue]Table[/blue] with the real table name.

Run the query by pressing [red]![/red] on the toolbar.

If you need more help just post. If you need more about normalisation search Tek-Tips.com and find all the best

 

[TrishMcK]

Thanks...

I am a trainer and although we run courses in relational database design we also need to run very simple courses on flat file databases for teachers who just really want to know the basics. Part of this course is to teach very simple calculations in queries and I was asked if you could use the average function.

From your response I can see that the answer must be no.

I will pass on the SQL and keep it for whenever I get the question again.

Thanks for your help...this is an excellent resource.

TrishMck

 

[JerryKlmns]

In order to use the Average built-in function, your data should be stored on the table in this way

Table : PupilsScore

PupilID
AssesmentId
AssesmentScore
.
.
.

and a simple SQL statement example for Average function is:

Select PupilID, Avg(AssesmentScore) as AvgScore
From PupilsScore
Group by PupilID;

But this is about normalising table strusture for relational databases

 

[MichaelRed]

also available here in (Tik-Tips) via the notorious search basAvgVal function ...




MichaelRed