Avatar of asrgrant
 asked on

Sum multiple fileds in a join but return one record that they belong to

Hi .. Im trying (as im a beginner) to join a lot of tables and sum one of the fields in one of the tables. See attached.

Im trying to display a record set that sums the amount of hours (tbl unit_time.hours) that a person (tbl personnel.personID) worked on a single task (tbl t_persontask.taskID). Ive had a go but the result set shows the hours worked , not summed, but also repeats the taskID in a repeat region. Im stuck.

I am using this ...

SELECT t_persontask.PersonID, personnel.*, tasking.*, customer.*, system_set.*
FROM personnel INNER JOIN (((tasking INNER JOIN t_persontask ON tasking.taskID = t_persontask.taskID) INNER JOIN system_set ON tasking.sys_setID = system_set.sys_setID) INNER JOIN customer ON tasking.customerID = customer.customerID) ON personnel.PersonID = t_persontask.personID
WHERE personnel.PersonID=MMColParam

... and I want to try and sum up the hours worked for an individual. The screen shot attached shows the output..(see screenshot.gif)
Microsoft SQL ServerMicrosoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Rey Obrero (Capricorn1)

use aggregate query

see this demo

Using wild cards (*) in this type of query can mess things up.

The best approach is to select the bare minimum specific fields that you need in order to display the needed results (generally all fields from all tables are not needed).

Once you do that, click the summation sign in the Query Builder and choose the field that you want to sum, and Group By the others.  

The reason for using a bare minimum of fields is that if other fields for a given person differ, you might not get the Sums per person -- they may appear grouped by other fields as well.

The idea is to isolate the field that you want to Sum, and make sure that any other selected fields for a given person have the values in the records.

Many thanks for that. I do use SUM and Grouping, guess im not doing well enough. I think im looking to do a sub-query suming the hours and then showing the rest of the detail
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

So here is where Im at ...

Main Query .......

FROM ((customer INNER JOIN tasking ON customer.customerID = tasking.customerID) LEFT JOIN system ON tasking.sysID = system.SysID) LEFT JOIN system_set ON tasking.sys_setID = system_set.sys_setID
WHERE tasking.tasklocation=1 AND NOT tasking.ttasktype='4' AND NOT tasking.tstatus='Closed'

I think I want to nest the  query below  in the one above, so to see the amount of hours (unit_time.hours) spent on this task (tasking.taskID)

SELECT tasking.taskID, Sum(unit_time.hours) AS SumOfhours
FROM tasking INNER JOIN unit_time ON tasking.taskID = unit_time.taskID
GROUP BY tasking.taskID

Hope this makes sense. Just having trouble SUMMING the unit_hours and outputting this as part of the main query

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question