Oldest date

[John]

I want to build a query to return a list of employees and the dates they
reached a certain level. In simple terms My table looks like this:

ID
Name
Level
Date

Unfortunatley, there are some other rows inserted for each member that
duplicates the levels (the table does some other stuff). So the data looks
like this:

1 Joe 3 01/01/2005
1 Joe 2 01/01/2004
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2007
2 Bob 4 01/01/2006
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002

I can get the first date for each employee by using something like this

SELECT ID,Name,Level,Min(date) as Dateoflevel
FROM MbrTbl
GROUP BY ID,Name,Level

How do I return the oldest date for each level for each employee so I would
get this:

1 Joe 3 01/01/2005
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

Michel Walsh suggests using a subquery. If this is a new concept, you may
appreciate a bit more info on how they work:
http://allenbrowne.com/subquery-01.html

 

[Lord Kelvan]

your query already gives you that data

min([date]) gives you the olderst date max([date]) gives you the most
recent date

i tested your query and it gives me

1 Joe 3 01/01/2005
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002

i just added order by in as well

SELECT ID, [Name], [Level], Min([date]) AS Dateoflevel
FROM MbrTbl
GROUP BY ID, [Name], [Level]
ORDER BY ID, [Level] DESC;

Regards
Kelvan