Query Totals

[Guest]

I'm creating a database for an organization at my school, and I've run into a
few roadblocks.

I've got a table, Members, with FirstName and LastName fields. I've got
another table, Activites, with club activities and dates. A junction table
holds Member Names, Activity, and points earned for each activity. Members
and Activites tables are linked in a Many-to-Many relationship.

So, here's my question. I need some way to create a query that will look up
Member Names, look up all corresponding values for the Points column in the
junction table, and add them up. And this needs to be done for each member.

So, is there any way of doing this?

 

[Marshall Barton]

I'm creating a database for an organization at my school, and I've run into a
few roadblocks.

I've got a table, Members, with FirstName and LastName fields. I've got
another table, Activites, with club activities and dates. A junction table
holds Member Names, Activity, and points earned for each activity. Members
and Activites tables are linked in a Many-to-Many relationship.

So, here's my question. I need some way to create a query that will look up
Member Names, look up all corresponding values for the Points column in the
junction table, and add them up. And this needs to be done for each member.

SELECT Members.LastName, Members.FirstName,
Sum(Junction.Points) As MemberPoints
FROM Members LEFT JOIN Junction
ON Members.keyfield = Junction.keyfield
GROUP BY Members.LastName, Members.FirstName

 

[Amy Blankenship]

I'd suggest that you add an autonumber to your Members table so you're not
repeating the name in the junction table (this also allows you to have more
than one member with the same name).

If you go into the query builder and select "new query in design view" in
the initial dialogue select your members table and your junction table.
Drag FirstName and LastName from the Members table to the grid at the
bottom, then drag the Points field from the junction table. Now click the
Totals button at the top (the button with the Sum symbol on it that looks
like sort of a cross between a Z and a backwards E). By default, all the
fields will have a new row somewhere in the middle that says "Group By." In
the Points column, change "Group By" to "Sum."

HTH;

Amy

 

[Guest]

Thank you, it worked perfectly.

I'd suggest that you add an autonumber to your Members table so you're not
repeating the name in the junction table (this also allows you to have more
than one member with the same name).

If you go into the query builder and select "new query in design view" in
the initial dialogue select your members table and your junction table.
Drag FirstName and LastName from the Members table to the grid at the
bottom, then drag the Points field from the junction table. Now click the
Totals button at the top (the button with the Sum symbol on it that looks
like sort of a cross between a Z and a backwards E). By default, all the
fields will have a new row somewhere in the middle that says "Group By." In
the Points column, change "Group By" to "Sum."

HTH;

Amy