Prev: Query and date selection
Next: Problem with query
From: Toria on 12 May 2010 15:48
I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

Thank you.
From: vanderghast on 12 May 2010 15:56
Many possible solutions, the easiest one is probably to do it in two
queries:

SELECT id, max(closeDate) AS mdate
FROM tableNameHere
GROUP BY id

saved as q1. Then:

SELECT a.*
FROM tableNameHere AS a INNER JOIN q1
ON a.id=b.id AND a.closeDate = q1.mdate



Other solutions at http://www.mvps.org/access/queries/qry0020.htm



Vanderghast, Access MVP


"Toria" <Toria(a)discussions.microsoft.com> wrote in message
news:7C6029D7-E9E2-434F-89EE-97D96AEE3680(a)microsoft.com...
>I have a root ID field. I have a close date field. There can be more than
>one
> record with the same root ID. I need to pull only one of them with the
> latest
> close date.
>
> Thank you.

From: John W. Vinson on 12 May 2010 16:35
On Wed, 12 May 2010 12:48:01 -0700, Toria <Toria(a)discussions.microsoft.com>
wrote:

>I have a root ID field. I have a close date field. There can be more than one
>record with the same root ID. I need to pull only one of them with the latest
>close date.
>
>Thank you.

A Subquery will do this for you; use a criterion on the close date field of

=(SELECT Max(X.[Close date] FROM yourtable AS X WHERE X.[Root ID] =
yourtable.[Root ID])

If there are two records with the same close date you'll get both. Does your
table have a Primary Key?
--

John W. Vinson [MVP]
From: Marshall Barton on 12 May 2010 16:36
Toria wrote:

>I have a root ID field. I have a close date field. There can be more than one
>record with the same root ID. I need to pull only one of them with the latest
>close date.

If you want the records in the table with the latest close
date across the entire table:

SELECT TOP 1 table.*
FROM table
ORDER BY [close date field] DESC

If you want the data in the records with latest close date
for each group id:

SELECT table.*
FROM table
WHERE table.[close date field] =
(SELECT Max(X.[close date field])
FROM table As X
WHERE X.[root id] = table.[root id])

or, probably faster:

SELECT table.*
FROM table INNER JOIN
(SELECT X.[root id],
Max(X.[close date field]) As Latest
FROM table As X
GROUP BY X.[root id]) As M
ON M.[close date field] = table.[close date field]
And M.[root id] = table.[root id])

Note that if there are multiple records with the same close
date (for a single group id), the query will return all thos
records.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Query and date selection
Next: Problem with query