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));
George_Hepworth here is what I am using:
Query 1:
SELECT Payroll.ID, Payroll.[Pay Run], Payroll.FCode
FROM Payroll
GROUP BY Payroll.ID, Payroll.[Pay Run], Payroll.FCode;Query 2:
SELECT DistinctJobFunctions.ID, DistinctJobFunctions.[Pay Run], DistinctJobFunctions.FCode
FROM DistinctJobFunctions;
GROUP BY DistinctJobFunctions.Pay_Run, DistinctJobFunctions.ID
HAVING (((Count(DistinctJobFunctions.FCode))>1));I get the error when running the second query and after I close the error, it highlights the word HAVING.
Thanks for all your help.
DataComplianceNerdThe group by should include each of the fields selected in the Select clause.
- Thanks, I missed one.
I saw your other post about ID being the primary key, and it is not. My ID field is their employee number as my identifier. Each line in my payroll table has an identifying record number that is auto sequenced. Each employee could have anywhere from 2-6 lines per pay period depending on their function.
I just need to figure out a way to determine those that are doing multiple functions.
"LEVEL clause"?
HAVING is indeed a reserved word in SQL, it is a way to say, in lay terms "only include records which meet this criteria", i.e. records "HAVING this criteria."
Can you provide the two SQL statements from your two queries? Maybe someone can spot something to help.