Latest occurrence for same person
in [Getting Started]
|
Prev: I have two Yes/No fields in a table (ContractorInstall, and InHouseInstall)
Next: [newbie] Expression for building a percentage
From: apollo11_1969 on 27 Jan 2010 07:09 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
From: NG on 27 Jan 2010 07:56 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
From: apollo11_1969 on 27 Jan 2010 09:17 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
From: Jerry Whittle on 27 Jan 2010 09:53 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
From: apollo11_1969 on 27 Jan 2010 10:43
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 |