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: Lynn Trapp on 28 Jan 2010 16:44 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
From: KenSheridan via AccessMonster.com on 28 Jan 2010 18:48
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/Forums.aspx/access-gettingstarted/201001/1 |