Solved

Calculate sum in a query

Posted on 2014-04-22
2
169 Views
Last Modified: 2014-04-22
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
Comment
Question by:rltomalin
2 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:rltomalin
Comment Utility
Wow - that did it!!

Thanks Dulton

Regards

Richard
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now