Selecting the 2nd highest date 1

[Delboy14]

Access 2002

I have a simple question to solve, I have a list of consultations in a table I would like to select the second last consultation for each person. IS there anyway to do this using the Max function, minus the highest date or another way?
the table is like this

PersonID ConsultationDate
1 10/11/2000
1 13/11/2000
1 01/12/2000
2 28/10/2000
3 14/10/2000
3 01/11/2000
3 21/12/2000

 

[jebry]

Hi!

I think you can do it with a subquery, something like:

Select PersonID, Max( ConsultationDate) as SecondDate From Table Where ConsultationDate < (Select Max(ConsultationDate), PersonId From Table Group By PersonID) Group By PersonID

I'm not totally sure about the syntax, but you can set it up in QBE using the subquery as the criteria for the ConsultationDate field.

hth
Jeff Bridgham

 

[shaddow]

It is possible but with som vb coding... ________

George

 

[THoey]

I didn't try this with a date field, but you should be able to alter it to make it work:

SELECT Max(Table1.Dollars) AS SecondDate
FROM Table1
WHERE Table1.Dollars <>
(
SELECT MAX(Table1.Dollars)FROM Table1
);
Terry M. Hoey

 

[Delboy14]

I did try it, but this does not eliminate the max date

 

[shaddow]

If u could add some vb coding this will be mutch easyer the it looks ________

George

 

[tlbroadbent]

You should be able to do this in a query without any VB code. The thing that has been missing in the suggested solutions is the WHERE clause in the sub query that correlates the sub query with the main query. You want to select the Max ConsultationDate per PersonID not for the entire table.

SELECT PersoneId, Max(ConsultationDate) AS SecondDate
FROM Table As a
WHERE ConsultationDate <
(SELECT MAX(ConsultationDate)
FROM Table
WHERE PersonID=a.PersonID)
GROUP BY PersonID; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jebry]

Thanks Terry!

I didn't know you could refer to the main query from the subquery. Learing all of the time!

Jeff Bridgham