Forum Discussion

DataComplianceNerd's avatar
DataComplianceNerd
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's avatar
    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));

     

     

George_Hepworth's avatar
George_Hepworth
Silver Contributor
Jan 30, 2020

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.

DataComplianceNerd's avatar
DataComplianceNerd
Copper Contributor
Jan 30, 2020

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 

 

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Jan 30, 2020

    DataComplianceNerd  Congratulations on finding a solution to the problem.

     

    Continued success with the project.

     

    Sorry it took so long to work out.

  • DataComplianceNerd's avatar
    DataComplianceNerd
    Copper Contributor
    Jan 30, 2020
    You are AMAZING!!! That is exactly what I needed. THANK YOU Soooooo much!!!! I appreciate your help.
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    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));