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));
DataComplianceNerdMy bad. I misunderstood. "ID" here is a Foreign Key to the employee table. In that case it would not be unique for each payroll record.
That said, you've add an additional criteria or two to that posted initially, I think:
" 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. "
So, it needs to be by Pay Date range, by Pay Codes, by Functions.
Right?
Sorry for the confusion. I don't necessarily care about the pay codes, just the functions. When I run the report I need anyone with more than one function anywhere within the specified date range. So if I select July 1 - August 30, I need a list of people to be created that have more than one function code.
DataComplianceNerd Congratulations on finding a solution to the problem.
Continued success with the project.
Sorry it took so long to work out.
- You are AMAZING!!! That is exactly what I needed. THANK YOU Soooooo much!!!! I appreciate your help.
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));DataComplianceNerdGive me a few minutes to work this over. Thanks.
I apologize for sending this in the wrong direction, George_Hepworth. Here is some actual data from my payroll table that I am working with. I would need employee 10567 to show up in my list because he has functions 215 & 221. I would not want 22301 to show up because they are only function 125 all the time. Does this make more sense of what I need?
Record Number ID Pay Run Pay Date Pay Code Pay Code Description Amount ASN Account # FCode Building Dept Object Code Grant # Percent Distributed 70 10567 299 20-Sep-19 1 $841.77 128 215 7 490 1280 0 1 71 10567 291 26-Jul-19 424 $382.00 199 221 66 0 1990 1618 72 10567 291 26-Jul-19 424 $418.00 199 221 66 0 1990 1609 73 22301 299 20-Sep-19 1 $942.27 163 125 8 0 1630 1009 74 22301 297 06-Sep-19 1 $942.27 163 125 8 0 1630 1009 75 62310 299 20-Sep-19 1 $225.38 124 513 13 0 1240 0 1 76 62310 299 20-Sep-19 1 $225.38 124 513 13 0 1240 0 1 77 62310 297 06-Sep-19 1 $225.38 124 513 13 0 1240 0 1 78 62310 297 06-Sep-19 1 $225.38 124 513 13 0 1240 0 1 79 62310 297 06-Sep-19 200 $182.98 194 221 13 0 1940 1519 80 62310 291 26-Jul-19 424 $382.00 199 221 66 0 1990 1618 81 62310 291 26-Jul-19 424 $418.00 199 221 66 0 1990 1609 82 62310 289 12-Jul-19 236 $392.10 192 221 66 0 1920 1609 83 73001 299 20-Sep-19 1 $225.38 124 513 74 0 1240 0 1 84 73001 299 20-Sep-19 1 $225.38 124 513 74 0 1240 0 1 85 73001 299 20-Sep-19 1 $225.38 124 513 74 0 1240 0 1 86 73001 297 06-Sep-19 1 $225.38 124 513 74 0 1240 0 1 87 73001 297 06-Sep-19 1 $225.38 124 513 74 0 1240 0 1 88 73001 297 06-Sep-19 1 $225.38 124 513 74 0 1240 0 1 89 73001 291 26-Jul-19 424 $382.00 199 221 66 0 1990 1618 90 73001 291 26-Jul-19 424 $418.00 199 221 66 0 1990 1609 DataComplianceNerd No problem. I'll see what I can do with that expanded table, but it would be helpful to have some REAL sample data to work with, not the kind of "over-simplified" data in the first post. Sometimes it does happen that trying to simplify actually is counter-productive because it masks real factors.