Sum of multiple columns

I have an old database that looks like this:

Job  Tech1  Hours1   Tech2  Hours2   Tech3  Hours3
A     Bob         1           Sam       2          Joe          3
B     Sam        2           Joe         3          Bob         1
C     Joe          3          Bob        1          Sam

The people in the database can fulfill different rolls on a job so they are put in different columns.

I need a query that figures out the total for each person.  In the above case, this would be:

Tech      Total Hours
Bob              3
Sam             6
Joe               9

I have this in an Access database with multiple queries that perform the calculation, but am looking for a way to simplify into 1 query.

Thanks!
MrBeedgeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Tony303Connect With a Mentor Commented:
Ok,

Let's try this..

SELECT Tech, SUM(Hours)  as TotalHours
FROM
(
SELECT Tech1 as Tech, ISNULL(Hours1,0) as Hours
FROM Table
UNION ALL
SELECT Tech2 as Tech, ISNULL(Hours2,0)  as Hours
FROM Table
UNION ALL
SELECT Tech3 as Tech, ISNULL(Hours3,0) as Hours
FROM Table
) a
GROUP BY Tech

Open in new window

0
 
Tony303Commented:
How about getting the 3 Techs and Hours separated out then brought together in a UNION?

Something like this....

SELECT Tech, SUM(Hours)  as TotalHours
FROM
(
SELECT Tech1 as Tech, Hours1 as Hours
FROM Table
UNION
SELECT Tech2 as Tech, Hours2 as Hours
FROM Table
UNION
SELECT Tech3 as Tech, Hours3 as Hours
FROM Table
) a
GROUP BY Tech

Open in new window

0
 
MrBeedgeAuthor Commented:
Tony -

Thanks for your reply.  I think some nulls in the hours (Hours1, Hours2, Hours3) are causing some issues.  If I only include the first Hours1 in the query, things total up fine.  When I add the Hours2 and Hours 3 using UNION, the Total Hours actually go down.  There will be some times in this database where the Tech Name is filled in, but no hours will be entered.

So, this is definitely simplified to what I have in my access database, but doesn't quite work,
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.

All Courses

From novice to tech pro — start learning today.