finding earliest record (97)

Topic

New Reply

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

Reply | Quote

Replies

Tha “Caveat” makes it a lot more interesting!

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;

Reply | Quote

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

Reply | Quote