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));
" 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.....
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 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.