Sorting a Table with an Outside Reference

[jsaliers]

I am trying to create a query that will sort a table by program number, then project number, and then by discipline. I have accomplished the the sorting by program and project number, but that is where the problem occurs. In this table, EngWo, each record tracks project and program, the engineer's name, and how many hours they worked. Their job discipline is saved in a different table, Ref_Employee. What I am having trouble with is getting the query to display for each project/program the number of hours each DISCIPLINE has worked. I have tried this several different ways and came up with different results. One result was sorting by each program and project, then listing each engineer's discipline and their hours, so there were multiple listings of the same job discipline for a particular project. Another result was to have it list only one discipline for each project (at least this one summed all of that discipline's hours correctly).

I know this is kinda hard to understand, so I tried to put an example below:

~~WHAT I WANT!!!!~~
Program E100
Project 47
Electrical - 7 hrs
Manufacturing - 8 hrs
Project 53
Product - 3 hrs
Manufacturing - 5 hrs
Tooling - 7 hrs
Program E101.....

~~WHAT I GOT!!~~
Program E100
Project 47
Electrical - 3 hrs
Electrical - 4 hrs
Manufacturing - 2 hrs
Manufacturing - 4 hrs
Manufacturing - 2 hrs
Project 53
Product - 2 hrs
Product - 1 hrs
Manufacturing - 3 hrs
Manufacturing - 2 hrs
Tooling - 3 hrs
Tooling - 2 hrs
Tooling - 2 hrs
Program E101.....

~~OR...~~
Program E100
Project 47
Electrical - 7 hrs
Project 53
Product - 3 hrs
Program E101.....

Any help is much appreciated!! Thanks in advance!

Jon

 

[Rick39]

Try GROUP BY

 

[jsaliers]

I guess I don't understand what you mean. I am not SQL proficient, but I know that GROUP BY is part of the SQL statement. The only criteria in the query that is not a GROUP BY is the hours, which is listed as a SUM. When I view the SQL statement, this is what I get:

SELECT Hours.ProgramNumber, Hours.Project, Ref_Employee.Job, Hours.WeekEnding, Sum(Hours.Hours) AS SumOfHours
FROM Ref_Employee INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
GROUP BY Hours.ProgramNumber, Hours.Project, Ref_Employee.Job, Hours.WeekEnding
ORDER BY Hours.ProgramNumber, Hours.Project, Ref_Employee.Job;

Thank you in advance!!

Jon Saliers

 

[jsaliers]

OK, this is the latest revision that I have, and this is pretty close to what I want, but it is still listing multiple records for each job discipline. In the On Open Event of the form I am using, I have the following SQL set up in my Report.RecordSource = statement:

SELECT DISTINCTROW Hours.ProgramNumber AS ProgramNumber, Hours.ProgramName AS ProgramName, Hours.Project AS ProjectNumber, Hours.ProjectName AS ProjectName, Ref_Employee.Job AS Job, Sum(Hours.Hours) AS SumOfHours
FROM Ref_Employee
INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
WHERE (WeekEnding >= #&quot; & dateStart & &quot;# and WeekEnding <= #&quot; & dateEnd & &quot;#)
GROUP BY Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job
ORDER BY Hours.ProgramNumber, Hours.Project, Ref_Employee.Job;

The dateStart and the dateEnd part works correctly, and is set up outside this statement. Any ideas? Thanks in advance!

Jon Saliers

 

[Rick39]

Try:

SELECT DISTINCTROW First(Hours.ProgramNumber) AS ProgramNumber, First(Hours.ProgramName) AS ProgramName, First(Hours.Project) AS ProjectNumber, First(Hours.ProjectName) AS ProjectName, Ref_Employee.Job AS Job, Sum(Hours.Hours) AS SumOfHours
FROM Ref_Employee
INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
WHERE (WeekEnding >= #&quot; & dateStart & &quot;# and WeekEnding <= #&quot; & dateEnd & &quot;#)
GROUP BY Ref_Employee.Job
ORDER BY Hours.ProgramNumber, Hours.Project, Ref_Employee.Job;

 

[jsaliers]

When I made the changes, I got an error:

&quot;You tried to execute a query that does not include the specified expression 'Hours.ProgramNumber' as part of an aggregate function.&quot;

Any ideas? Thanks in advance!

Jon Saliers

 

[scriverb]

Change the ORDER BY line to the following:
ORDER BY
First(Hours.ProgramNumber), First(Hours.Project), Ref_Employee.Job;

If you have aggregated in some other way than GroupBy then you must identify the ORDER BY in the same way as the Select.
Bob Scriver

 

[jsaliers]

Well, the error is no longer there. However, I am sure that this is not completely correct because there is only five records that appear. There should be many more than that. It is listing only one discipline per project, and only one project per program. The hours that are listed for each discipline appear to be the total sum of hours for the entire program. Any ideas?

I may be chasing a lost cause here. Thank you again for all your help, it is much appreciated.

Jon Saliers