Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    Austin TX
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Full-Time / Part-Time Historical Report

    Good Afternoon,

    I am trying to create a query, in MS Access 2007, that will produce a history of full-time / part-time statuses for each employee. The issue I am having is that the table that houses the historical data is basically a snapshot of that status anytime a transaction is made, regardless of whether or not it is a status change. Therefore, if someone gets a pay increase but does not change FT/PT status, the table holds the rate change and a snapshot of the status.

    I only need to retrieve the effective date of the first time that field changes between the actual status of FT/PT.

    I have attached an Excel doc showing my originating data and desired query results.
    Any suggestions on how to do this?

    Thanks in advance for your assistance.

    -Ronnie =)
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Sep 2011
    Location
    Charmhaven NSW
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts

    I dont think a queary can do this

    Hi
    I dont think a query can do this. At first i thought a group query migth do the trick . . but there is no value/column that can be grouped to create a change point.

    Seems like you will need to resrt to a loop through a list of records

    i have done this elsewhere by creating a listbox with the raw data - sort - in your case - on emp-id / date / FT-PT
    set this up on a form and have a button to do the processing
    you can loop through the listbox using the column attribute

    So . . using your XLS sheet as the template and calling the listbox ListCurrentData
    you would refer to the first FT-PT (for Mary Jones) as Me.ListCurrentData.Column(3,1)
    3 = 4 column . . as in 0 1 2 3
    1= 2nd row . . if you keep the header labels . . else use (3,0)

    I would be inclined to create a record in new table for each change record . . or if you can have a field in the currentdata table - (yes/no) flag it as a change record.
    then you can query the new table . .or filter the data by the flag field


    cheers
    PaulG

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a demo that does the job with two queries.

    The first query has two calculated fields.

    For each event we need to find the previous event for that employee. That is the largest date less than the current one. But if it is the first record for that employee there will be no previous, so just use the current date.

    PreviousDate: Nz(DMax("[Action Date]","tblTimeRecords","([Emp ID]=" & [Emp ID] & ") and ([Action Date] <#" & Format([Action Date],"mm/dd/yyyy") & "#)"),[action Date])

    Next we need to find the FT_PT status for that employee on the Previous Date.

    PreviousStatus: DLookUp("[FT_PT]","tblTimeRecords","([Emp ID]=" & [Emp ID] & ") and ([Action Date] =#" & Format([PreviousDate],"mm/dd/yyyy") & "#)")


    Query two then find any records in query1 where:
    The two dates are the same- the first record for the employee, or
    The FT_PT status is not the same as the PreviousStatus.

    You may be able to remove the Format function from the queries. We need that in Aust as we use dd/mm/yyyy date format.
    Attached Files Attached Files
    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
  •