Get last two entries using date field 1

[Eprice]

Hi,
I have no idea how to do this so I thought using a query would work. I need to get the last two entries for a claim number using the date field. Then I could use min and max for the fields on my report. If I have dates of 02/03/05, 11/04/05, 05/17/06, 08/10/06 & 11/02/06 I only want the records for dates 08/10/06 & 11/02/06. The dates would always be different for each claim number. I'm sure this is easy but I'm not figuring it out. Thanks
Lisa

 

[Remou]

Have you tried Select Top 2, with a descending Order By on the date?

 

[lespaul]

Try something like this:

SELECT A.ClaimNumber, B.MinDate, C.MaxDate FROM
TableName A
INNER JOIN (SELECT ClaimNumber, MIN(DateField) As MinDate From TableName GROUP BY ClaimNumber) B ON A.ClaimNumber = B.ClaimNumber
INNER JOIN (SELECT ClaimNumber, MAX(DateField) As MaxDate FROM TableNmae GROUP BY ClaimNumber) C ON A.ClaimNumber = C.ClaimNumber

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Eprice]

I have no idea about using SQL in a query. Here is what my query has:

SELECT tblReserve.[DL_CLAIM_#], tblReserve.RDATE, tblReserve.RINDEM_AMT, tblReserve.RLEGAL_AMT
FROM tblReserve
WHERE (((tblReserve.[DL_CLAIM_#])=[Forms]![frmDLedit]![DL PICK LIST]));

I want to select the last two RDate fields. There could be two or twenty, who knows.

Lisa

 

[Remou]

Does:

[tt]SELECT Top 2 tblReserve.[DL_CLAIM_#], tblReserve.RDATE, tblReserve.RINDEM_AMT, tblReserve.RLEGAL_AMT
FROM tblReserve Order By tblReserve.RDATE Desc
WHERE (((tblReserve.[DL_CLAIM_#])=[Forms]![frmDLedit]![DL PICK LIST]));[/tt]

Give what you want?

 

[lespaul]

My bad, misunderstood what was being asked for!!

Leslie

 

[PHV]

SELECT TOP 2 [DL_CLAIM_#], RDATE, RINDEM_AMT, RLEGAL_AMT
FROM tblReserve
WHERE [DL_CLAIM_#]=[Forms]![frmDLedit]![DL PICK LIST]
ORDER BY RDATE DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

Remou, I got an order by error. PVH that works perfectly. You guys are great. Thanks
Lisa