x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 180

# 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
0
rltomalin
1 Solution

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];
``````
0

Author Commented:
Wow - that did it!!

Thanks Dulton

Regards

Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.