Sum of Hours by Discipline, NOT Person 1

[jsaliers]

Okay, one last crack at this problem I am having...

I have two tables, with the following relevant fields:

Hours: ProgramNumber, ProgramName, Project, ProjectName, Engineer, Hours

Ref_Employee: Name, Job

Engineer in table Hours and Name in table Ref_Employee are the same, and relate a job discipline (Job in table Ref_Employee) to an engineer. I am trying to make a query that sorts the Hours table by Program and Project, then sums the hours for each project by discipline.

Problems:
- There can be multiple records for an individual, so a mechanical engineer might have 3 entries for a sum of 12 hrs. 12Hrs must be the sum that shows up.
- There can be multiple records for a discipline, so two different electrical engineers may each have 3 records, for a total for both engineers of 20 hrs. 20Hrs must be the sum that shows up for Electrical Engineer.
- Ther can be records for each project from different job disciplines, so there may be a total of 16 hrs spent by different electrical engineers, and a total of 32 hrs spent by different manufacturing engineers, and it must show as follows:

Program E100 - Engineering Other
Project 487 - Routine Maintenance
Electrical Engineer 16 hrs
Manufacturing Engineer 32 hrs

Program E134 - PD-Lab
Project 512 - Audit Testing
Lab Technician 8 hrs
Electrical Engineer 6 hrs
Maintenance 56 hrs

I have tried many reiterations of this, and cannot seem to get it correct. The closest I have gotten it is to sum the hours for each engineer, so if two different techs worked on the same project, there will be two records in the query for techs for that project.

Any help is greatly appreciated. I am extremely frustrated with this project, and am behind the 8-Ball on this project.

Thank you in advance!!

Jon Saliers

 

[psemianonymous]

Try this SQL statement (paste into the query builder SQL view):

SELECT Sum(Hours) AS TOTAL_HOURS, Project, ProjectName, Engineer
FROM Hours
GROUP BY Project, ProjectName, Engineer

That should give you your intended sum. Hopefully. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[jsaliers]

This sorts by name, and not discipline. This is addressed by Problem 2 in my original submission. I don't have a problem in summing hours per person, but I have a problem summing hours per discipline.

Any ideas are greatly appreciated. Thanks in advance.

Jon

 

[Rick39]

Unless there is a discipline field in your SQL to group by, I don't think you're going to get there

 

[jsaliers]

Here is what I have right now:

SELECT Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job, Hours.Engineer, Sum(Hours.Hours) AS myHours
FROM Ref_Employee INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
GROUP BY Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job, Hours.Engineer;

I have tried many reiterations of this, and cannot seem to get it correct. The closest I have gotten it is to sum the hours for each engineer, so if two different techs worked on the same project, there will be two records in the query for techs discipline for that project.

Any ideas are greatly appreciated. Thanks in advance.

Jon

 

[psemianonymous]

Take out the "Hours.Engineer" field from your statement (both times) and it should then (again hopefully) work.

SELECT Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job, Sum(Hours.Hours) AS myHours
FROM Ref_Employee INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
GROUP BY Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job;

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[jsaliers]

Well, I must say that I am very pleased. It finally works!! Thank you so much for all your help. It is much appreciated.

Thanks again!

Jon Saliers