Finding Multiple Values

Occasional Contributor

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. 




16 Replies


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

Thank you for your help, @George Hepworth. I followed your instructions and everything is working as expected, except when I try to run the second query. I am getting an error message of: The LEVEL clause includes a work or argument that is misspelled or missing." It doesn't like the HAVING as it appears on a list of reserved words for Access.



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


@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.

@George Hepworth 


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.





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.

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 NumberIDPay RunPay DatePay CodePay Code DescriptionAmountASNAccount #FCodeBuildingDeptObject CodeGrant #Percent Distributed
701056729920-Sep-191 $841.77 1282157490128001
711056729126-Jul-19424 $382.00 19922166019901618 
721056729126-Jul-19424 $418.00 19922166019901609 
732230129920-Sep-191 $942.27 1631258016301009 
742230129706-Sep-191 $942.27 1631258016301009 
756231029920-Sep-191 $225.38 124513130124001
766231029920-Sep-191 $225.38 124513130124001
776231029706-Sep-191 $225.38 124513130124001
786231029706-Sep-191 $225.38 124513130124001
796231029706-Sep-19200 $182.98 19422113019401519 
806231029126-Jul-19424 $382.00 19922166019901618 
816231029126-Jul-19424 $418.00 19922166019901609 
826231028912-Jul-19236 $392.10 19222166019201609 
837300129920-Sep-191 $225.38 124513740124001
847300129920-Sep-191 $225.38 124513740124001
857300129920-Sep-191 $225.38 124513740124001
867300129706-Sep-191 $225.38 124513740124001
877300129706-Sep-191 $225.38 124513740124001
887300129706-Sep-191 $225.38 124513740124001
897300129126-Jul-19424 $382.00 19922166019901618 
907300129126-Jul-19424 $418.00 19922166019901609 


@DataComplianceNerdGive me a few minutes to work this over. Thanks.

best response confirmed by DataComplianceNerd (Occasional Contributor)

@George Hepworth 


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



You are AMAZING!!! That is exactly what I needed. THANK YOU Soooooo much!!!! I appreciate your help.

@DataComplianceNerd  Congratulations on finding a solution to the problem.


Continued success with the project.


Sorry it took so long to work out.