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
|
|||
|
|||
Latest occurrence for same person
Hi. I have db with a number of people and they do a certain type of a course,
but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#2
|
|||
|
|||
Latest occurrence for same person
Hi,
you can create a groups query grouped bij person and course and show the max(date) gr NG "apollo11_1969" wrote: Hi. I have db with a number of people and they do a certain type of a course, but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#3
|
|||
|
|||
Latest occurrence for same person
Hi. Thanks NG. What is a 'groups query'. Looked it up in help and in my
Access for dummies book, but cdn't find it. FYI I'm on Access 2003. Apollo11_1969 "NG" wrote: Hi, you can create a groups query grouped bij person and course and show the max(date) gr NG "apollo11_1969" wrote: Hi. I have db with a number of people and they do a certain type of a course, but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#4
|
|||
|
|||
Latest occurrence for same person
Check out Totals queries in Help. If you look at the SQL statement for a
Totals query, you'll see a "Group By" clause. That's probaby why NG called it a 'groups' query. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apollo11_1969" wrote: Hi. Thanks NG. What is a 'groups query'. Looked it up in help and in my Access for dummies book, but cdn't find it. FYI I'm on Access 2003. Apollo11_1969 "NG" wrote: Hi, you can create a groups query grouped bij person and course and show the max(date) gr NG "apollo11_1969" wrote: Hi. I have db with a number of people and they do a certain type of a course, but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#5
|
|||
|
|||
Latest occurrence for same person
Hi Jerry. Thanks. Yes, I had tried a variety of max/last etc, in a no. of the
query fields - e.g. I put in 'last' in the date field, but there are still multiple occurrences for each person when I run query. NG hadn't said what to group by though [for the person & course]. There needs to be something that links the date to the person I guess, so that it's the most recent date [for that course] for that employee. Thanks Apollo11_1969 "Jerry Whittle" wrote: Check out Totals queries in Help. If you look at the SQL statement for a Totals query, you'll see a "Group By" clause. That's probaby why NG called it a 'groups' query. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apollo11_1969" wrote: Hi. Thanks NG. What is a 'groups query'. Looked it up in help and in my Access for dummies book, but cdn't find it. FYI I'm on Access 2003. Apollo11_1969 "NG" wrote: Hi, you can create a groups query grouped bij person and course and show the max(date) gr NG "apollo11_1969" wrote: Hi. I have db with a number of people and they do a certain type of a course, but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#6
|
|||
|
|||
Latest occurrence for same person
Switch your query to SQL View and past the following in the editor. Change
the Field Names and Table Names appropriately. SELECT PersonName, Course, Max(CourseDate) AS LastDate FROM YourTable GROUP BY PersonName, Course; -- Lynn Trapp MCP, MOS, MCAS "apollo11_1969" wrote: Hi Jerry. Thanks. Yes, I had tried a variety of max/last etc, in a no. of the query fields - e.g. I put in 'last' in the date field, but there are still multiple occurrences for each person when I run query. NG hadn't said what to group by though [for the person & course]. There needs to be something that links the date to the person I guess, so that it's the most recent date [for that course] for that employee. Thanks Apollo11_1969 "Jerry Whittle" wrote: Check out Totals queries in Help. If you look at the SQL statement for a Totals query, you'll see a "Group By" clause. That's probaby why NG called it a 'groups' query. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "apollo11_1969" wrote: Hi. Thanks NG. What is a 'groups query'. Looked it up in help and in my Access for dummies book, but cdn't find it. FYI I'm on Access 2003. Apollo11_1969 "NG" wrote: Hi, you can create a groups query grouped bij person and course and show the max(date) gr NG "apollo11_1969" wrote: Hi. I have db with a number of people and they do a certain type of a course, but they have to have refresher courses every couple of years. How do I get a list that has one occurrence of each name with the last time they took the course? e.g. what I have: Name Course Date Joe 1A 01/05/2006 Mary 1A 05/08/2007 Joe 1A 05/08/2007 Rich 1A 07/04/2001 Mary 1A 06/07/2009 What I want: Joe 1A 05/08/2007 Mary 1A 06/07/2009 Rich 1A 07/04/2009 |
#7
|
|||
|
|||
Latest occurrence for same person
Are you returning columns other than Name, Course and Date in the query? If
so that would account for the multiple rows. In that case you need to use a subquery to get the latest date per person/course and use this to restrict the rows returned, e.g. SELECT * FROM [YourTable] AS T1 WHERE T1.[Date] = (SELECT MAX(T2.[Date]) FROM [YourTable] AS T2 WHERE T2.[Name] = T1.[Name] AND T2.[Course] = T1.[Course]); Note how the two instances of the table are given aliases T1 and T2 to differentiate them and allow the subquery to be correlated with the outer query on Name and Course. If, on the other hand, you are returning only the three columns then grouping the query by name and course and returning the MAX date as Lynn described is the way to do it. BTW Name and Date are not good column names as they are the names of the built in Name property and Date function in access, so should be avoided. Use more specific names like StudentName and CourseDate. Ken Sheridan Stafford, England apollo11_1969 wrote: Hi Jerry. Thanks. Yes, I had tried a variety of max/last etc, in a no. of the query fields - e.g. I put in 'last' in the date field, but there are still multiple occurrences for each person when I run query. NG hadn't said what to group by though [for the person & course]. There needs to be something that links the date to the person I guess, so that it's the most recent date [for that course] for that employee. Thanks Apollo11_1969 Check out Totals queries in Help. If you look at the SQL statement for a Totals query, you'll see a "Group By" clause. That's probaby why NG called it [quoted text clipped - 29 lines] Mary 1A 06/07/2009 Rich 1A 07/04/2009 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201001/1 |
Thread Tools | |
Display Modes | |
|
|