Separating values and summing

  • Thread starter Thread starter Haji
  • Start date Start date
H

Haji

Hello,

I am trying to sum the number of hours that our employees
have work on various projects. Their is a field called
Employee that usually has the first name of one of our
six employees. however, there are two exceptions to this
two employees have worked on a project. In these cases,
the data is entered as Steve/Pam or Steve/Milly. What I
need to do is to extract the times that Pam or Milly has
worked on a job (It isn't necessary to get Steve's
values) and then sum these hours with the times that Pam
or Milly has individually worked on a project. Right now
my data looks like this:

Employee Hours
Milly 10
Pam 6
Jill 3
John 5
Steve/Milly 3
Steve/Pam 2

What I want is for the "Steve/Milly" hours of 3 to be
added to Milly's existing 10 hours and the "Steve/Pam"
hours of 2 to be added to Pam's 6 hours and have the
Steve/Milly and Steve/Pam to dissapear. The data should
then look like:

Employee Hours
Milly 13
Pam 8
Jill 3
John 5

Can anyone help?

Thanks,

Haji
 
You need a table of each employee's name.
tblEmployees.Employee
Jill
John
Milly
Pam

Assuming your original table is named tblEmpWorkHrs, you can create a
cartesian query with this sql.

SELECT tblEmployees.Employee,
Sum(tblEmpWorkHrs.Hours) AS SumOfHours
FROM tblEmployees, tblEmpWorkHrs
WHERE tblEmpWorkHrs.Employee
Like "*" & [tblEmployees].[employee] & "*"
GROUP BY tblEmployees.Employee;

This may break if one employee's name is included in another employees like
Sara and Sarah or Al and Sal. If that is the case then use:
SELECT tblEmployees.Employee,
Sum(tblEmpWorkHrs.Hours) AS SumOfHours
FROM tblEmployees, tblEmpWorkHrs
WHERE ((("/" & [tblEmpWorkHrs]![Employee] & "/")
Like "*/" & [tblEmployees].[employee] & "/*"))
GROUP BY tblEmployees.Employee;
 
Back
Top