Record (row) totals and average - how?

H

hello

Can anyone help us with this. One of my students wants to do the following
in Access:

A student has a table with the following records in:

Name Mark1 Mark2 Mark3
John 21 53 47
Mary 43 23 66
Ali 61 53 23

How, using a query, can you produce row totals and an average score against
the name

i.e the result of running the query should be:

Name Total Average
John 121 40.33
Mary 132 44.00
Ali 137 44.33

Thanks
 
T

TPratt

I'm sure there are better ways to do this, but you can use SQL similar to
the SQL I pasted in below.

SELECT [Name], Sum([Mark1]+[Mark2]+[Mark3]) AS [Total], [Total]/3 AS
[Average]
FROM TableName
GROUP BY Name;
 
H

hello

Hi thanks for this, but this is a parameter query - it asks me to input
values. My student already has a table and simply needs to summarise data in
each row.

TPratt said:
I'm sure there are better ways to do this, but you can use SQL similar to
the SQL I pasted in below.

SELECT [Name], Sum([Mark1]+[Mark2]+[Mark3]) AS [Total], [Total]/3 AS
[Average]
FROM TableName
GROUP BY Name;




hello said:
Can anyone help us with this. One of my students wants to do the
following
in Access:

A student has a table with the following records in:

Name Mark1 Mark2 Mark3
John 21 53 47
Mary 43 23 66
Ali 61 53 23

How, using a query, can you produce row totals and an average score against
the name

i.e the result of running the query should be:

Name Total Average
John 121 40.33
Mary 132 44.00
Ali 137 44.33

Thanks
Click to expand...
Click to expand...
 
M

Marsela

Hi!

You can create a query, with student name, mark1, mark2,
mark3, create 2 fields
Total:(mark1+mark2+mark3) and average:(mark1+mark2+mark3)/3
run the query:

I don't know if you are looking for something else, just
explain more.

Thanks,
MArsela
-----Original Message-----
Hi thanks for this, but this is a parameter query - it asks me to input
values. My student already has a table and simply needs to summarise data in
each row.

TPratt said:
I'm sure there are better ways to do this, but you can use SQL similar to
the SQL I pasted in below.

SELECT [Name], Sum([Mark1]+[Mark2]+[Mark3]) AS [Total], [Total]/3 AS
[Average]
FROM TableName
GROUP BY Name;




hello said:
Can anyone help us with this. One of my students wants to do the
following
in Access:

A student has a table with the following records in:

Name Mark1 Mark2 Mark3
John 21 53 47
Mary 43 23 66
Ali 61 53 23

How, using a query, can you produce row totals and an
Click to expand...
average score
against
the name

i.e the result of running the query should be:

Name Total Average
John 121 40.33
Mary 132 44.00
Ali 137 44.33

Thanks
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

This is an issue with a un-normalized table structure. Each Mark should be a
record in a related table. In your example, I would expect to see 9 records
in a table like:
Student Test Score
John 1 21
John 2 53
John 3 47
Mary 1 43
etc
Your query would then be
SELECT Student, Sum(Score) as TotalScore, Avg(Score) as AverageScore
FROM tblNormalizedScores
GROUP BY Student;

The current structure doesn't allow you to add more tests without modifying
tables, forms, queries, expressions, reports,.... That would be a very
horrible way to create and maintain an application.

--
Duane Hookom
MS Access MVP


Marsela said:
Hi!

You can create a query, with student name, mark1, mark2,
mark3, create 2 fields
Total:(mark1+mark2+mark3) and average:(mark1+mark2+mark3)/3
run the query:

I don't know if you are looking for something else, just
explain more.

Thanks,
MArsela
-----Original Message-----
Hi thanks for this, but this is a parameter query - it asks me to input
values. My student already has a table and simply needs to summarise data in
each row.

TPratt said:
I'm sure there are better ways to do this, but you can use SQL similar to
the SQL I pasted in below.

SELECT [Name], Sum([Mark1]+[Mark2]+[Mark3]) AS [Total], [Total]/3 AS
[Average]
FROM TableName
GROUP BY Name;




Can anyone help us with this. One of my students wants to do the
following
in Access:

A student has a table with the following records in:

Name Mark1 Mark2 Mark3
John 21 53 47
Mary 43 23 66
Ali 61 53 23

How, using a query, can you produce row totals and an average score
against
the name

i.e the result of running the query should be:

Name Total Average
John 121 40.33
Mary 132 44.00
Ali 137 44.33

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Top