Forum Discussion

Copper Contributor

Jan 28, 2020

Solved

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

access

office 365

George_Hepworth
Jan 30, 2020
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));

DataComplianceNerd

Copper Contributor

Jan 29, 2020

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.

DataComplianceNerd

Copper Contributor

Jan 29, 2020

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.

George_Hepworth
Silver Contributor
Jan 29, 2020
DataComplianceNerdThe group by should include each of the fields selected in the Select clause.
- DataComplianceNerd
  Copper Contributor
  Jan 29, 2020
  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.
  - George_Hepworth
    Silver Contributor
    Jan 29, 2020
    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.