Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    finding earliest record (97)

    Once again I find myself in over my head.

    The challenge:

    I have a table named "tblCheck"

    tblCheck contains 25,000+ records with the following fields
    strSSN
    strJob
    dtmCheckDate

    I need to create a query (or table) that will give me a list of the individual's job history (based on strSSN). For example 123-23-1234 may have a total of 15 checks from three different jobs in the tblChecks table. My goal is to then have three records for employee 123-23-1234 that contain:
    strSSN
    strJob
    dtmCheckDate (where the check date is the earliest check for that job)

    For example
    strSSN strJob dtmCheckDate
    123231234 OneEast 1/1/03
    123231234 OneEast 1/8/03
    123231234 OneEast 1/15/03
    123231234 OneEast 1/22/03
    123231234 OneEast 1/30/03
    123231234 TwoWest 2/6/03
    123231234 TwoWest 2/13/03
    123231234 ThreeJob 2/20/03

    Should result in:
    strSSN strJob dtmCheckDate
    123231234 OneEast 1/1/03
    123231234 TwoWest 2/6/03
    123231234 ThreeJob 2/20/03

    Caveat:

    If the last entry had been:
    strSSN strJob dtmCheckDate
    123231234 OneEast 2/20/03

    Then the result should be:
    strSSN strJob dtmCheckDate
    123231234 OneEast 1/1/03
    123231234 TwoWest 2/6/03
    123231234 OneEast 2/20/03

    Which shows the employee returned to the the first job after a short stint at "TwoWest"

    Is this possible? Have I provided enough information? Thanks in advance for you ideas and guidance.

    Respectfully,

    kwvh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding earliest record (97)

    Tha "Caveat" makes it a lot more interesting! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Create two queries: the first query is based on tblChek and gets the "previous" dtmCheckDate for each record.

    SELECT tblCheck.strSSN, tblCheck.dtmCheckDate, tblCheck.strJob, (SELECT Max(T.dtmCheckDate) From tblCheck As T WHERE T.dtmCheckDate<tblCheck.dtmCheckDate And T.strSSN = tblCheck.strSSN) AS dtmPrevDate
    FROM tblCheck;

    Save this query as qrySelPrev.

    Create a second query based on qrySelPrev and tblCheck:

    SELECT qrySelPrev.strSSN, qrySelPrev.strJob, qrySelPrev.dtmCheckDate
    FROM qrySelPrev LEFT JOIN tblCheck ON (qrySelPrev.dtmPrevDate = tblCheck.dtmCheckDate) AND (qrySelPrev.strSSN = tblCheck.strSSN)
    WHERE ((([qrySelPrev].[strJob]<>[tblCheck].[strJob] Or [tblCheck].[strJob] Is Null)=True))
    ORDER BY qrySelPrev.strSSN, qrySelPrev.dtmCheckDate;

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: finding earliest record (97)

    Hans,

    Thanks for the queries. I will work on them this weekend and hopefully let you know that they work, and what a brilliant person you are.

    Have a great day!

    Ken

Posting Permissions

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