Calculate sum in a query

Hello experts - hope you can help with this little question.

I have 3 tables – tblClients, tblJobs and tblJobVolLink.

The relationships are:
One to Many - Clients to Jobs
Many to Many – Jobs to Volunteers

The following query:
SELECT tblClients.[Client ID], tblJobs.[Short description], tblJobVolLink.HoursWorked
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) INNER JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID];

Returns a row for every volunteer, with their separate hours – eg:
ID       Description     Hours
23          Job 1                   2
23          Job 1                   3
.....

What I require is for the query to return a single row for each Job with the TOTAL volunteer hours – eg:
ID      Description      Hours
23       Job 1                 5
.....

Can you please suggest the SQL that would give me that.

Regards

Richard
rltomalinAsked:
Who is Participating?
 
DultonConnect With a Mentor Commented:
SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) As [HrsWorked]
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) INNER JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
group by  tblClients.[Client ID], tblJobs.[Short description];

Open in new window

0
 
rltomalinAuthor Commented:
Wow - that did it!!

Thanks Dulton

Regards

Richard
0
All Courses

From novice to tech pro — start learning today.