Calculating Sums

[John]

Hi,

I have a database tracking students and classes they have
taken. I have one table Training that includes the
information of the class (name, number, number of hours).
I also have a results table that tracks the results of the
students and the classes they have taken.

What I want to be able to do is to run a query that will
provide me with a sum of the total hours taken for each
class. For instance if 10 people took 'course 1' and 15
people took 'course 2' and 'course 1' lasts for 1 hour
and 'course 2' for 2 hours, i want the query to tell me
Course 1 = 10 total hours, Course 2 = 30 total hours. Any
help would be greatly appreciated thank you!!!!!!

 

[MarkD]

For me, the best solution (the one that seems the most
natural to me) is to create a table that lists the course
and hours:

Course Hours
================
Course1 1
Course2 2
Course3 1.5
(etc.)

Then, I'd do a group by query on the students table
(grouped on the course) and count the number of students
taking each course. Finally, I'd join that query to the
table above, and multiply the count by the hours field.

There are other ways to do this, but again, this process
is what seems the most natural to me. YMMV.

-M

 

[John Vinson]

Hi,

I have a database tracking students and classes they have
taken. I have one table Training that includes the
information of the class (name, number, number of hours).
I also have a results table that tracks the results of the
students and the classes they have taken.

What I want to be able to do is to run a query that will
provide me with a sum of the total hours taken for each
class. For instance if 10 people took 'course 1' and 15
people took 'course 2' and 'course 1' lasts for 1 hour
and 'course 2' for 2 hours, i want the query to tell me
Course 1 = 10 total hours, Course 2 = 30 total hours. Any
help would be greatly appreciated thank you!!!!!!

Use a Totals query - create a query based on your table, change it to
a Totals query by clicking the Greek Sigma icon (looks like a sideways
W). Group By the Course and Sum the number of hours.