Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011

    Question Unanswered: MIMIC VLookup in access

    I have searched through a ton of threads but none complete what I am looking for.
    I am trying to Mimic VLOOKUP in Access.
    I have two tables. 1st table has the persons employee#[emp#] and hours worked[hours].
    [Emp#] ------ [Hours]
    1 ------------------10
    2 ------------------15

    2nd table has the hours until the next level of pay[HoursMax] and the pay step associated with said hours maximum[StepPay]
    [HoursMax]----- [StepPay]
    0 ---------------------1
    So once the employee reaches 30 hours they move to pay step # 2, 50 hours moves to step# 3 etc...

    What I am trying to accomplish is to be able to display the number of hours remaining until the next step for each employee and continue to display the next #remaining hours until they reach the step after.

    Employee 1 currently has 10 hours so they have 20 hours remaining to reach pay step # 2 (which = 30 hours).
    Employee 2 currently has 15 hours so they have 15 hours remaining to reach pay step #2 etc...
    Once the employee reaches the maximum number of hours of [StepPay]#2 the remaining hours starts to calculate the difference till the next [StepPay]#3 hours max.

    I assume I can manipulate DLookup & IIF functions, but I'm surprised there isn't a VLookup Function in access.

  2. #2
    Join Date
    May 2004
    New York State
    Hi, Dee Jay, and welcome to the forum.

    Bottom line: there is no VLookup work-alike in Access. I've had the same headache several times, and I've been forced to admit defeat.

    Assuming 67 employees and 36 pay steps, where IIF() won't cut it, the most efficient way (in my humble opinion) is the DO loop. You would open the two tables as OpenTable recordsets. Table2 should be indexed on MaxHours DESC, and be sure to open the index. Your VBA would go from record to record of Table1, and identify which is the appropriate paystep from Table2.

    You can decide how you want to handle it from there. You can make a temp table which can then be the recordsource for a report, for example.

    Good luck,

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    You can run Excel Functions from within Access:

    Access 2000 (also works in Access 2003, I believe)
    How to call Excel functions from within Access 2000

    Access 2007
    Using Excel 2007 Functions in Access 2007

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jan 2002
    Bay Area
    The Access code below may give you some help in making this happen. It gives you a look-up table with all the hours and also a field
    to do the calculation for how many hours to the next higher pay step. From your post, I assumed the pay-step range starts with 0-29 hours,
    then 20 hours thereafter.
    Good luck.

    Function Make_HoursTbl()
    Dim rst As Recordset
    Dim strSQL As String, myTblName As String
    Dim intPayStep As Integer, intEmpHours As Integer, intMaxHours As Integer
    Dim i As Integer, j As Integer
        myTblName = "PayStepLookup"
        strSQL = "Create Table " & myTblName & " (EmpHours INTEGER, PayStep INTEGER, MaxHours INTEGER)"
        DoCmd.SetWarnings False
        On Error Resume Next
        DoCmd.DeleteObject acTable, myTblName
        On Error GoTo 0
        DoCmd****nSQL strSQL
        DoCmd.SetWarnings True
        Set rst = CurrentDb.OpenRecordset(myTblName, dbOpenTable)
        'create the first pay step that has 0 thru 29 hours worked
        intEmpHours = 0
        intPayStep = 1
        intMaxHours = 30
        For intEmpHours = 0 To 29
            rst!EmpHours = intEmpHours
            rst!PayStep = intPayStep
            rst!MaxHours = intMaxHours
        intEmpHours = 30
        intPayStep = 2
        intMaxHours = 50
        '20-hour pay steps--for all steps beyond pay step #1
        For i = 1 To 5      'adjust count on this line for the number pay steps needed
            For j = 1 To 20
                rst!EmpHours = intEmpHours
                rst!PayStep = intPayStep
                rst!MaxHours = intMaxHours
                intEmpHours = intEmpHours + 1
            intPayStep = intPayStep + 1
            intMaxHours = intMaxHours + 20
        Set rst = nothing
    End Function

  5. #5
    Join Date
    Nov 2011
    Thanks JerryDal, I greatly appreciate the help. This was a good step in teaching me a few things about VBA.. After countless hours of trying to manipulate, I was not able to get the code to work as I needed. Had to settle for a work around which involved a bunch of If statements and setvalue parameters.

  6. #6
    Join Date
    Apr 2004
    metro Detroit
    This can be done with two queries.

    The first query determines the next pay step for the employee:
    SELECT tblEmployee.EmpoyeeNumber, Min(tblPaySteps.StepPay) AS MinOfStepPay
    FROM tblEmployee, tblPaySteps
    WHERE (((tblEmployee.hours)<[hoursmax]))
    GROUP BY tblEmployee.EmpoyeeNumber;

    The second query subtracts the employee hours from the next pay step maxhours:

    SELECT tblEmployee.EmpoyeeNumber, [HoursMax]-[Hours] AS HoursToGo
    FROM (tblEmployee INNER JOIN qryNextPayStep ON tblEmployee.EmpoyeeNumber = qryNextPayStep.EmpoyeeNumber) INNER JOIN tblPaySteps ON qryNextPayStep.MinOfStepPay = tblPaySteps.StepPay;

  7. #7
    Join Date
    Nov 2011
    Rogue, I will give it a try.
    Thanks! everyone for your help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts