Jan 28 2020 12:12 PM - edited Jan 28 2020 12:31 PM
I have a table that has thousands of rows in it. I need to write a query to pull out lines where a person is serving more than one function. Below in my example I want Micky below to appear in my query because he has multiple functions. Minnie would not appear because she has the same function the whole time.
Example:
ID | First | Last | Date | Amount | Function |
12833 | Micky | Mouse | 07/26/19 | 1,219 | 226 |
12833 | Micky | Mouse | 07/12/19 | 1,219 | 227 |
12833 | Micky | Mouse | 09/20/19 | 1,219 | 228 |
56025 | Minnie | Mouse | 08/23/19 | 1,219 | 226 |
56025 | Minnie | Mouse | 09/06/19 | 1,219 | 226 |
56025 | Minnie | Mouse | 08/09/19 | 1,219 | 226 |
Jan 29 2020 08:25 AM
@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));
Jan 29 2020 11:27 AM
Jan 29 2020 11:45 AM
"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.
Jan 29 2020 12:34 PM
@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.
Jan 29 2020 01:38 PM
@DataComplianceNerdThe group by should include each of the fields selected in the Select clause.
Jan 29 2020 01:44 PM
Jan 29 2020 02:07 PM
" 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.
Jan 30 2020 05:22 AM
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.....
Jan 30 2020 05:54 AM
@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?
Jan 30 2020 06:48 AM
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.
Jan 30 2020 06:58 AM
@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.
Jan 30 2020 07:08 AM
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 |
Jan 30 2020 07:40 AM
@DataComplianceNerdGive me a few minutes to work this over. Thanks.
Jan 30 2020 08:19 AM
Solution
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));
Jan 30 2020 09:39 AM
Jan 30 2020 10:44 AM
@DataComplianceNerd Congratulations on finding a solution to the problem.
Continued success with the project.
Sorry it took so long to work out.
Jan 30 2020 08:19 AM
Solution
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));