01-28-2020 12:12 PM - edited 01-28-2020 12:31 PM
01-28-2020 12:12 PM - edited 01-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.
01-29-2020 08:25 AM
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
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
GROUP BY DistinctJobFunctions.FirstName, DistinctJobFunctions.LastName
01-29-2020 11:27 AM
01-29-2020 11:45 AM
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.
01-29-2020 12:34 PM
@George Hepworth here is what I am using:
SELECT Payroll.ID, Payroll.[Pay Run], Payroll.FCode
GROUP BY Payroll.ID, Payroll.[Pay Run], Payroll.FCode;
SELECT DistinctJobFunctions.ID, DistinctJobFunctions.[Pay Run], DistinctJobFunctions.FCode
GROUP BY DistinctJobFunctions.Pay_Run, DistinctJobFunctions.ID
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.
01-29-2020 01:44 PM
01-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.
01-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.....
01-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.
01-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.
01-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.
01-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|
01-30-2020 08:19 AMSolution
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
GROUP BY Payrolldata.ID, Payrolldata.FunctionCode;
Save this as UniqueEmployeeFunctionCodes
Use it in this query.
SELECT UniqueEmployeeFunctionCodes.ID, Count(UniqueEmployeeFunctionCodes.FunctionCode) AS MultipleFunctionCodes
GROUP BY UniqueEmployeeFunctionCodes.ID