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." Each line in my payroll table has an identifying record number that is auto sequenced."
If they are unique, even though they are not Primary Keys, including them will force one record for each, so that means it can't be included. Use the employee's ID, or Employee Number, so that you get one record per employee.
Once you get how the Group By works, it'll be totally obvious, but it can be a bit opaque at first.
Here is the setup for my Payroll Table:
I load an Excel file I get from my accounting department each week. the highlighted fields are lookups to other tables in my database. ID is linked to my Employee file where ID is my primary key and one line per employee. My payroll table has thousands of lines in it and just keeps growing each week when I get a new file. My ultimate goal is to create a report of those that have more than one Function Code for a range of pay dates. An employee may have multiple Pay Codes, but under the same function. I need the ones that have multiple functions as well.
Does this make sense? I have been racking my brain on how to make this work and just can't get it.....