COUNT on 2 Coloumns

[vijay]

Hi,
I have 2 tables one Employee Other Vacation Design is as follows
tbl Emp
EmpID, FirstName, LastName
1 FN LN

tbl Vacation
EmpID, VacationDate, VacationTypeID
1 11/01/2004 1
1 11/02/2004 1
1 11/10/2004 5
1 11/11/2004 5
Note: VacationTypeID 1 = FullDay 5 = HalfDay 2HalfDays = 1 FullDay

How can I have output showing EmpID, FirstName, LastName and Count
VacationDate based on Both VacationTypeID
Result
EmpID FirstName LastName, FullDays, HalfDays TotalDays
1 FN LN 2 2 3

Thanks for your time and Energy
Vijay

 

[Gerald Stanley]

The following is air-code but try something along the lines of

SELECT E.empId, E.firstName, E.lastName, Sum(IIf(V.vacationTypeId = 1,1,0))
As fullDays, Sum(IIf(V.vacationTypeId = 5,1,0)) As halfDays,
Sum(IIf(V.vacationTypeId = 1,1,0.5)) As totalDays
FROM tblEmp AS E INNER JOIN tblVacation AS V ON E.empId = V.empId
GROUP BY E.empId, E.firstName, E.lastName

The above will restrict the list of employees to only those who have taken
holidays. You may have to replace the INNER JOIN with a LEFT JOIN to get a
list of all employees.

Hope This Helps
Gerald Stanley MCSD