Finding Multiple Values

[DataComplianceNerd]

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

[#2]

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

[#3]

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.

[#4]

@DataComplianceNerd 

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

[#5]

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

[#6]

@DataComplianceNerdThe group by should include each of the fields selected in the Select clause.

[#7]

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.

[#8]

@DataComplianceNerd 

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

[#9]

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

[#10]

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

[#11]

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. 

[#12]

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

[#13]

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

[#14]

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

[#15 · Accepted solution]

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

[#16]

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

[#17]

@DataComplianceNerd  Congratulations on finding a solution to the problem.

Continued success with the project.

Sorry it took so long to work out.