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=MMColPa
ORDER BY taskref DESC
... and I want to try and sum up the hours worked for an individual. The screen shot attached shows the output..(see screenshot.gif)
taskingj-oins.gif
taskingj-oins2.gif
screenshot.gif
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=MMColPa
ORDER BY taskref DESC
... and I want to try and sum up the hours worked for an individual. The screen shot attached shows the output..(see screenshot.gif)
taskingj-oins.gif
taskingj-oins2.gif
screenshot.gif
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.
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.
ASKER
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
ASKER
So here is where Im at ...
Main Query .......
SELECT *
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'
ORDER BY taskref DESC
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
Main Query .......
SELECT *
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'
ORDER BY taskref DESC
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
see this demo
http://www.youtube.com/watch?v=AykV47ObmFA