Re: Separating values and summing

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 14:29:17 -0500

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; 

-- 
Duane Hookom
MS Access MVP
--
"Haji" <anonymous@discussions.microsoft.com> wrote in message 
news:077001c4b7a0$6ac3e550$a301280a@phx.gbl...
> 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
>
>
>