Forum Discussion
Finding Multiple Values
Based on this sample data, these queries return three employee IDs with 2 (or more) Function Codes within the range of the data. To apply date range filtering, you can join this query back to the payroll data and filter it on date.
SELECT Payrolldata.ID, Payrolldata.FunctionCode
FROM Payrolldata
GROUP BY Payrolldata.ID, Payrolldata.FunctionCode;Save this as UniqueEmployeeFunctionCodes
Use it in this query.
SELECT UniqueEmployeeFunctionCodes.ID, Count(UniqueEmployeeFunctionCodes.FunctionCode) AS MultipleFunctionCodes
FROM UniqueEmployeeFunctionCodes
GROUP BY UniqueEmployeeFunctionCodes.ID
HAVING (((Count(UniqueEmployeeFunctionCodes.FunctionCode))>1));
DataComplianceNerd
You can do this with two queries.
the first will group People by jobs, so you get one row per person per job.
SELECT Table1.FirstName, Table1.LastName, Table1.JobFunction
FROM Table1
GROUP BY Table1.FirstName, Table1.LastName, Table1.JobFunction;
Save that as "DistinctJobFunctions" and use this query in a second one.
SELECT DistinctJobFunctions.FirstName, DistinctJobFunctions.LastName, Count(DistinctJobFunctions.JobFunction) AS CountOfJobFunction
FROM DistinctJobFunctions
GROUP BY DistinctJobFunctions.FirstName, DistinctJobFunctions.LastName
HAVING (((Count(DistinctJobFunctions.JobFunction))>1));