Forum Discussion
Finding Multiple Values
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 |
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 Replies
- 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.
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.
"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.
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));
