CODEWITHSUNDEEP
sqlms-access
LNB
LNB

Reputation: 53

SQL SELECT query with JOIN, SUM and GROUP BY

I have 5 tables in a MS Access databse: tblMember, tblPoint, tblRace, tblRaceType and tblResult. (All of which have primary keys.) tblPoint contains (RaceTypeID, Position, Points) fields.

What I want to do is look at all the races that the members participated in, see what position they came (stored in tblResult) and see if those positions score points (as defined in tblPoint). I then want to add up all the points for each member and show these, along with the member name in my query...

Is this possible? I came up with my best shot at this SQL query below:

SELECT Sum(tblPoint.Points) AS SumOfPoints, Count(tblRace.RaceID) AS CountOfRaceID,     
tblMember.MemberName, tblPoint.Points
FROM ((tblRaceType INNER JOIN tblPoint ON tblRaceType.RaceTypeID = tblPoint.RaceTypeID)    
INNER JOIN tblRace ON tblRaceType.RaceTypeID = tblRace.RaceTypeID) INNER JOIN   
(tblMember INNER JOIN tblResult ON tblMember.MemberID = tblResult.MemberID) ON    
tblRace.RaceID = tblResult.RaceID
GROUP BY tblMember.MemberName, tblPoint.Points
ORDER BY tblPoint.Points DESC;

Is anyone able to point me in the right direction at all?

Upvotes: 2

Views: 8421

Answers (1)

Martin
Martin

Reputation: 1622

I'd say this

GROUP BY tblMember.MemberName, tblPoint.Points
ORDER BY tblPoint.Points DESC;

should probably be more like this:

GROUP BY tblMember.MemberName
ORDER BY Sum(tblPoint.Points) DESC;

Also, remove tblPoint.Points at the end of your select. This is just a single point value, you want the sum.

Grouping by points means that you'll get one row per member and point value they scored - probably not what you intended.

Upvotes: 3

Related Questions