Adding the total of a column

T

Tdahlman

I am going to have a table with several entries. One of the fields in this
table is going to be "Quality Points". What I need to do is in a query
someone, Add up all the number in "Quality Points." How do I go about this?

Thanks in advance,
Travis
 
J

Jerry Whittle

You don't. At least not in a table. An Excel spreadsheet maybe but not in a
database table.

If you are using a database, you do not want several entries, AKA columns or
fields across with points for different things in each field. Such as:

Person QP1 QP2 QP3 etc.
Jim 2 3 1
Tim 1 1 1

What you want is a table something like this:
Person QualityPoints Catagory
Jim 2 QP1
Jim 3 QP2
Jim 1 QP3
Tim 1 QP1
Tim 1 QP2
Tim 1 QP3

Then you could run a query something like
SELECT Person Sum(QualityPoints) as SumOfQualityPoints
FROM YourTable
Group By Person;

That will return:
Person SumOfQualityPoints
Jim 6
Tim 3

Further you do not store the sum of the points. When you need to know, you
run the query.
 
T

Tdahlman

I am doing this as a Grade Point analyzer.
So my table will look like:
Course Grade Credits QualityPoints
ACC212 4 3 12 (4 * 3)
ECO210 2 3 6
MTH201 4 5 20
ETC

I'll need a total of the Credits column and the Quality Points column.
So I would need:
Credits = 11
QualityPoints = 38

Then I could figure that 38 / 11 = 3.46, which would be the current GPA.

Thanks,
Travis
 
J

Jerry Whittle

Whoops! My first answer was off as I wrongly concluded that you were putting
the courses in different fields.

Still you do not want to store the Quality Points in the table. Why? Let's
say that you find an error with a Grade and change it. If you forget to also
change the Quality Point value, you have inconsistant data. Instead you
should compute the Quality Points as needed in a query like so. Note: I'm
assuming that you want to do things by Student.

SELECT GradePoint.StudentID, GradePoint.Course,
GradePoint.Grade, GradePoint.Credits,
[Grade]*[Credits] AS QualityPoints
FROM GradePoint;

Now you were talking about a total. This could be done in a similar query OR
using the same query in a Report by using the sorting and grouping options.
Below will give you the Quality Points by Student.

SELECT GradePoint.StudentID,
Sum(GradePoint.Grade) AS SumOfGrade,
Sum(GradePoint.Credits) AS SumOfCredits,
Sum([Grade]*[Credits]) AS QualityPoints
FROM GradePoint
GROUP BY GradePoint.StudentID;

One other thought: If the Course always has the same Credits, you don't need
to put in the Credit values in the GradePoint table each time. You could have
a table with Course_Information which would have the Course and Credits in
it. Then you could join the tables on the Course field. The SQL would look
something like:

SELECT GradePoint.Course,
GradePoint.Grade,
Courses.Credits,
[Grade]*[Credits] AS QualityPoints
FROM GradePoint, Courses
WHERE Courses.Credits = GradePoint.Credits ;
 
Top