Record (row) totals and average - how?

  • Thread starter Thread starter hello
  • Start date Start date
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
 
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;
 
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...
 
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...
 
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...
 
Back
Top