Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    New Jersey
    Thanked 0 Times in 0 Posts

    Current And Previous Record (Access 2000)

    I am trying to run a query off a table called BasePayHist. It contains the following fields; emp#,base pay,startdate and enddate. The problem I am having is when I run a history report it gives me all the records in the basePayHist table for each employee. What I would like is a report of only the most current (the most current would have a blank end date) and the previous record. Can someone help me with this sql statement?
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Current And Previous Record (Access 2000)

    This may be a case where a self-join would do the trick. Start by creating a query that pulls just the records with a blank enddate, and then calculate an enddate for the prior record by subtracting one from the start date of that record. Then do a join between the calculated enddate in the query and the table, and pull those records that match the calculated enddate with their enddate - you probably also need to join on the employee number as well. Try doing this in the query designer rather than using SQL - most people find that approach much more intuitive. One small fly in the ointment to think about. What about cases where a person has just started, and there is not prior base payrate record????

Posting Permissions

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