Why don't this query work???

[jacob]

Can you do only one aggregation function in a query? That makes no sense. Why
don't my query work?

SELECT e.employeeid, Sum(s.SickTime) as SickTime, Sum(v.VacationTime) as
VacationTime, Sum(p.PersonalTime) as personaltime

FROM (((Department INNER JOIN (Center INNER JOIN Employee ON Center.CenterID
= Employee.CenterID) ON Department.DepartmentID = Employee.DepartmentID)
INNER JOIN PersonalTime ON Employee.EmployeeID = PersonalTime.EmployeeID)
INNER JOIN SickTime ON Employee.EmployeeID = SickTime.EmployeeID) INNER JOIN
VacationTime ON Employee.EmployeeID = VacationTime.EmployeeID

where d.departmentid=1
group by e.employeeid

I just copied the from statement provided by Access. I couldn't get my own
to work.

FROM ((((((Department d inner join employee e on d.employeeid=e.employeeid)
inner join Center c on e.centerid = c.centerid)
inner join sicktime s on s.employeeid = e.employeeid)
Inner join personaltime on p.employeeid = e.employeeid)
inner join vacationtime v on v.employeeid=e.employeeid)
inner join personaltime on p.employeeid = e.employeeid)

 

[KARL DEWEY]

Your were abbreviating table names and INNER JOIN everywhere. If all joins
are INNER join then there must be a matching record in every table. If an
employee has no personal time the INNER joins would not pull their record at
all.

SELECT Employee.EmployeeID, Sum(SickTime.SickTime) AS SickTime,
Sum(VacationTime.VacationTime) AS VacationTime,
Sum(PersonalTime.PersonalTime) AS personaltime
FROM (((Department INNER JOIN (Center INNER JOIN Employee ON Center.CenterID
= Employee.CenterID) ON Department.DepartmentID = Employee.DepartmentID) LEFT
JOIN PersonalTime ON Employee.EmployeeID = PersonalTime.EmployeeID) LEFT JOIN
SickTime ON Employee.EmployeeID = SickTime.EmployeeID) LEFT JOIN VacationTime
ON Employee.EmployeeID = VacationTime.EmployeeID
WHERE (((Department.DepartmentID)=1))
GROUP BY Employee.EmployeeID;