How do you produce record (row) totals and averages

  • 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
 
Well, you don't store them. That would be redundnat. You already have the
data there. When you need it, just calculate it. If you store it, what
happens if one of those "marks" changes? How does Access know to go back
and update the table?

What you want to do is add a new column to your query with something like...

StudentTotal: [Mark1] +[Mark2] + [Mark3]


And

StudentAverage: ([Mark1] +[Mark2] + [Mark3])/3



If the number of "marks" will vary, then the underlying database structure
is flawed. The databse structure you used assumes there will always and
only be 3 marks per student. If that is not the case (and I doubt it is)
then they should restructure their tables. The "marks" would be stored in a
new table. If a student had one assignment completed, they'd have one
record in the "tblMarks". If the student had completed five assignemnts,
they's have five records. There would be a separate table to store the
student information.



Rick B
 
Thanks but I don't see how this can work. If you run it, it asks you for
StudentTotal, Mark1, Mark2 and Mark3.

The data is already in the tables. How do you add Mark1, Mark2 and Mark3
together if they are already in a table?

Cheers
Rick B said:
Well, you don't store them. That would be redundnat. You already have
the
data there. When you need it, just calculate it. If you store it, what
happens if one of those "marks" changes? How does Access know to go back
and update the table?

What you want to do is add a new column to your query with something
like...

StudentTotal: [Mark1] +[Mark2] + [Mark3]


And

StudentAverage: ([Mark1] +[Mark2] + [Mark3])/3



If the number of "marks" will vary, then the underlying database structure
is flawed. The databse structure you used assumes there will always and
only be 3 marks per student. If that is not the case (and I doubt it is)
then they should restructure their tables. The "marks" would be stored in
a
new table. If a student had one assignment completed, they'd have one
record in the "tblMarks". If the student had completed five assignemnts,
they's have five records. There would be a separate table to store the
student information.



Rick B







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...
 
Did you add a new column to your query and in the FIELD: type exactly what I
put earlier? If your fields are named "mark1" and "mark2... as you stated,
then this will not ask you for anything.

Paste your sql query back if you wnat an exact answer that you can copy and
paste.



Rick B





hello said:
Thanks but I don't see how this can work. If you run it, it asks you for
StudentTotal, Mark1, Mark2 and Mark3.

The data is already in the tables. How do you add Mark1, Mark2 and Mark3
together if they are already in a table?

Cheers
Rick B said:
Well, you don't store them. That would be redundnat. You already have
the
data there. When you need it, just calculate it. If you store it, what
happens if one of those "marks" changes? How does Access know to go back
and update the table?

What you want to do is add a new column to your query with something
like...

StudentTotal: [Mark1] +[Mark2] + [Mark3]


And

StudentAverage: ([Mark1] +[Mark2] + [Mark3])/3



If the number of "marks" will vary, then the underlying database structure
is flawed. The databse structure you used assumes there will always and
only be 3 marks per student. If that is not the case (and I doubt it is)
then they should restructure their tables. The "marks" would be stored in
a
new table. If a student had one assignment completed, they'd have one
record in the "tblMarks". If the student had completed five assignemnts,
they's have five records. There would be a separate table to store the
student information.



Rick B







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...
Click to expand...
 
Back
Top