Returning the Most Recent 2 records in a table based on Date Field

[Shaiguy]

I have a table containing the following fields:

ProjectUpdateID (PrimaryKey)
ProjectID
UpdateDate

I would like to create a Query in Ms Access 2000 which will return me
the most recent 2 records (and also show the above 3 fields)

So suppose the table looks like this:
ProjectUpdateID, ProjectID, UpdateDate
1,119,2/13/1999
2,119,2/24/2000
3,119,1/19/1998
4,119,1/21/2003
5,120,8/22/2001
6,120,9/12/2002
7,120,9/11/2001
The Resulting query should return the following, because these are the
2 most recent records "for each project ID":

ProjectUpdateID, ProjectID, UpdateDate
4,119,1/21/2003
2,119,2/24/2000
6,120,9/12/2002
7,120,9/11/2001
I was able to get it to work for "THE" most recent date by projectID,
by not including ProjectUpdateID in the query, and using "MAX", but as
soon as I include the ProjectUpdateID field in immediately expands and
return all the records for that ProjectID.

Please Help ? Is there a function I don't know about ?
Please email me as well as post.

Best
Shai

ny****@yahoo.com

[Rich Protzel]

To return the most recent record entered you can use a Group By query
and and in the Totals row select the Last function (aggragate function)
for your ID field. The Last function returns the last record entered.
Note: if you have a date field and 8/1/03 was entered before the record
with 7/1/03, and the record with 7/1/03 was the last record entered,
that it the record that will be returned with the Last function. you
could set criteria for a date field like select the last record entered
between 7/1/03 and 8/1/03. For the second to last record, unless you
have some key field for ranking records by order of first to last, you
will have to set up your own criteria, or maybe add a new field for
ranking the records.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Winterbottom]

"Shaiguy" <ny****@yahoo.com> wrote in message
news:81**************************@posting.google.c om...

I have a table containing the following fields:

ProjectUpdateID (PrimaryKey)
ProjectID
UpdateDate

I would like to create a Query in Ms Access 2000 which will return me
the most recent 2 records (and also show the above 3 fields)

So suppose the table looks like this:
ProjectUpdateID, ProjectID, UpdateDate
1,119,2/13/1999
2,119,2/24/2000
3,119,1/19/1998
4,119,1/21/2003
5,120,8/22/2001
6,120,9/12/2002
7,120,9/11/2001
The Resulting query should return the following, because these are the
2 most recent records "for each project ID":

ProjectUpdateID, ProjectID, UpdateDate
4,119,1/21/2003
2,119,2/24/2000
6,120,9/12/2002
7,120,9/11/2001

This is aircode but something like this should do it:

-----------------------------------------------------------
select a1.ProjectUpdateID, a1.ProjectID, a1.UpdateDate
from
(
select ProjectUpdateID, ProjectID, UpdateDate
from mytable
group by ProjectUpdateID, ProjectID, UpdateDate
) as a1
inner join
(
select ProjectUpdateID, ProjectID, UpdateDate
from mytable
group by ProjectUpdateID, ProjectID, UpdateDate
) as a2
on a1.ProjectID = a2.ProjectID
and a1.UpdateDate <= a2.ProjectDate
group by a1.ProjectUpdateID, a1.ProjectID, a1.UpdateDate
having count(*) <3
order by a1.ProjectID, a1.UpdateDate desc

----------------------------------------------------------