A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Group By or MAX Question



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 08:48 PM posted to microsoft.public.access.queries
Toria
external usenet poster
 
Posts: 50
Default Group By or MAX Question

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.
  #2  
Old May 12th, 2010, 08:56 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Group By or MAX Question

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" wrote in message
...
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.


  #3  
Old May 12th, 2010, 09:35 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Group By or MAX Question

On Wed, 12 May 2010 12:48:01 -0700, 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.

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]
  #4  
Old May 12th, 2010, 09:36 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Group By or MAX Question

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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.