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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Oldest date
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 |
#2
|
|||
|
|||
Oldest date
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" wrote in message ... 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 |
#3
|
|||
|
|||
Oldest date
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 |
Thread Tools | |
Display Modes | |
|
|