Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    Austin TX
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need a query or some sort of function that will accomplish the following:

    I have a table that houses records by pay period for each employee. Each record has a snapshot of what the Employee's status is (full-time or part-time). Some records do not have a status because there was no paycheck.

    This is an example of the table:


    Empl ID FT/PT PeriodEndDate
    129 F 1/1/2008
    129 3/16/2008
    129 P 9/16/2008

    I need for a query or function of sorts to run through the records for each EmployeeID and find the null values and make update them to the value just preceding it. In this case, I would like to see the null value in the record dated 03/16/2008 to be "F" like in the record with period ending 1/1/2008.

    Not sure where to start on this and any help you could offer would be fantastic.

    Thanks in advance,

    -Ronnie

  2. #2
    New Lounger
    Join Date
    Sep 2010
    Location
    Austin TX
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apparently the text got skewed when I posted, so I have attached an example of the table on this word doc.
    Attached Files Attached Files

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    What database are you using?
    Rui
    -------
    R4

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Ronnie G View Post
    I need a query or some sort of function that will accomplish the following:

    I have a table that houses records by pay period for each employee. Each record has a snapshot of what the Employee's status is (full-time or part-time). Some records do not have a status because there was no paycheck.

    This is an example of the table:


    Empl ID FT/PT PeriodEndDate
    129 F 1/1/2008
    129 3/16/2008
    129 P 9/16/2008

    I need for a query or function of sorts to run through the records for each EmployeeID and find the null values and make update them to the value just preceding it. In this case, I would like to see the null value in the record dated 03/16/2008 to be "F" like in the record with period ending 1/1/2008.

    Not sure where to start on this and any help you could offer would be fantastic.

    Thanks in advance,

    -Ronnie
    I would use VBA code to do this. TRy something like:
    Code:
    Dim rs as DAO.Recordset, sFTPT as String
    Set rs = CurrentDB.OpenRecordset("SELECT * FROM tablename ORDER BY [Empl ID], PeriodEndDate")
    Do While not rs.Eof
       If IsNull(rs!FTPT) Then
            rs.Edit
            rs!FTPT = sFTPT
            rs.Update
        End If
        sFTPT = rs!FTPT
        rs.MoveNext
    Loop
    Set rs = Nothing

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I would just add one thing to Patt's answer.

    Is there a possibility that there might be EmpIDs where the first (or perhaps only) record is missing its Status.

    e.g.

    Empl ID FT/PT PeriodEndDate
    129 F 1/1/2008
    129 3/16/2008
    129 P 9/16/2008
    130 1/1/2009
    131 F 1/1/2009
    131 1/6/2101

    In this situation Patt's code would update 130 to P using the value from Emp 129.
    It might be safer to reset the value of sFTPT each time you move to a new Employee.
    Regards
    John



Posting Permissions

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