Help with Query Problem...

[Dorv]

So, I've created a simple database for tracking In-Service hours for the lifeguards at my facility. Only three tables, one for the Employees, one that holds the information about each class, and an Enrollment table that combines ClassID's and EmployeeID's.

I need a query that will tell me how many Hours (from the Classes table) each employee has. My solution was to create a table that included EmployeeID from the EmployeeTable and the Hours from the Classes table, and to use the Sum aggregate function.

Which works great, with one exception... The query only shows EmployeeID's that have having matching information in SumOfHours (which is how I now know that aggregate functions work).

Can anyone suggest a solution or work around?

 

[PHV]

You may consider a LEFT JOIN instead of an INNER JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

If (as I interpret your request) you need to include all employees whether or not they have hours, use a left join. Ir that's not enough info, post your sql and someone will be happy to assist you.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Dorv]

I've tried changing the joins before, to no avail...

What sql code would help... Here's the code for the exisiting (semi-working) query:

SELECT EmpTable.EmployeeID, Sum(ClassDates.Hours) AS SumOfHours
FROM EmpTable INNER JOIN ((Classes INNER JOIN ClassDates ON Classes.ClassID = ClassDates.ClassID) INNER JOIN Enrollment ON Classes.ClassID = Enrollment.ClassID) ON EmpTable.EmployeeID = Enrollment.EmployeeID
GROUP BY EmpTable.EmployeeID;

 

[lespaul]

Because of Access weird join rules, not sure where to put it in the SQL, but you can in the query design grid, right click on the relationship line between EmployeeID in the EmployeeTable and Enrollment table and choose the option:

Include all records from Employee and only those that match from Enrollment.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Dorv]

THANKS to All...

Finally got it... It was more than just the Join b/w the Enrollment and EmpTable, every join had to be adjusted just right

(also b/w Enrollment and Classes (right join to Enrollment) and b/w Classes and ClassDates [qry that finds the hours per class, and also filters the specific dates I'm looking for] (left join to classes...)

 

[Dorv]

Here's the code that worked:

SELECT EmpTable.EmployeeID, Sum(ClassDates.Hours) AS SumOfHours, EmpTable.LastName, EmpTable.FirstName
FROM EmpTable LEFT JOIN ((Classes LEFT JOIN ClassDates ON Classes.ClassID = ClassDates.ClassID) RIGHT JOIN Enrollment ON Classes.ClassID = Enrollment.ClassID) ON EmpTable.EmployeeID = Enrollment.EmployeeID
GROUP BY EmpTable.EmployeeID, EmpTable.LastName, EmpTable.FirstName;