Group By Query
in [Queries]
|
From: Les on 22 Jan 2010 07:36 Hello I'm struggling to explain this but here goes. I have a table which contains 6 fields which make up the location, 1 field which gives the type of recording and 1 field which gives the date of the recording. Lets say there are 3 types of recording so I have 3 records for each location. I'd like the query to return the most recent date for each location regardles of type of recording but I still need to include the type of recording data in the query. Many thanks
From: golfinray on 22 Jan 2010 08:20 When you do group by, (the little E on the toolbar) and the group by comes up on the query grid, you can change that to Last on your date field. That should give you the last date. -- Milton Purdy ACCESS State of Arkansas "Les" wrote: > Hello > I'm struggling to explain this but here goes. > > I have a table which contains 6 fields which make up the location, 1 field > which gives the type of recording and 1 field which gives the date of the > recording. > > Lets say there are 3 types of recording so I have 3 records for each location. > I'd like the query to return the most recent date for each location > regardles of type of recording but I still need to include the type of > recording data in the query. > > Many thanks > >
From: Les on 22 Jan 2010 08:29 Sorry, no I'm still getting a record for each recording type, not the latest one only. Cheers. "golfinray" wrote: > When you do group by, (the little E on the toolbar) and the group by comes up > on the query grid, you can change that to Last on your date field. That > should give you the last date. > -- > Milton Purdy > ACCESS > State of Arkansas > > > "Les" wrote: > > > Hello > > I'm struggling to explain this but here goes. > > > > I have a table which contains 6 fields which make up the location, 1 field > > which gives the type of recording and 1 field which gives the date of the > > recording. > > > > Lets say there are 3 types of recording so I have 3 records for each location. > > I'd like the query to return the most recent date for each location > > regardles of type of recording but I still need to include the type of > > recording data in the query. > > > > Many thanks > > > >
From: John Spencer on 22 Jan 2010 10:09 NO! LAST does not return the latest record by date. It returns the last record that the database engine accesses (for the group) while creating the recordset. If you want the latest date, you use the MAX function. You need a query to give you the maximum date per location and then use that to get identify the records to return. SELECT FIELD1, Field2, Field3, Field4, field5, field6 , Max(DateField) as LatestDate FROM SomeTable GROUP BY FIELD1, Field2, Field3, Field4, field5, field6 Then you use that in another query where you join the seven fields of the query to the corresponding seven fields of the table. You can then include whatever fields you want to display from the table. If you don't know how to construct the query in SQL view or can't figure out from the above how to build the query in design view, construct a query that uses the 8 fields involved and then post the sql of a query that shows the eight fields involved. Someone should be able to help you create the necessary query. See: Getting a related field from a GroupBy (total) query at: http://www.mvps.org/access/queries/qry0020.htm John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County golfinray wrote: > When you do group by, (the little E on the toolbar) and the group by comes up > on the query grid, you can change that to Last on your date field. That > should give you the last date.
From: golfinray on 22 Jan 2010 11:03
John, I knew that. Thanks for the correction. I'm way past brain dead this morning. Milt -- Milton Purdy ACCESS State of Arkansas "John Spencer" wrote: > NO! > > LAST does not return the latest record by date. It returns the last record > that the database engine accesses (for the group) while creating the > recordset. If you want the latest date, you use the MAX function. > > You need a query to give you the maximum date per location and then use that > to get identify the records to return. > > SELECT FIELD1, Field2, Field3, Field4, field5, field6 > , Max(DateField) as LatestDate > FROM SomeTable > GROUP BY FIELD1, Field2, Field3, Field4, field5, field6 > > Then you use that in another query where you join the seven fields of the > query to the corresponding seven fields of the table. > > You can then include whatever fields you want to display from the table. > > If you don't know how to construct the query in SQL view or can't figure out > from the above how to build the query in design view, construct a query that > uses the 8 fields involved and then post the sql of a query that shows the > eight fields involved. Someone should be able to help you create the necessary > query. > > See: > Getting a related field from a GroupBy (total) query > at: > http://www.mvps.org/access/queries/qry0020.htm > > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > golfinray wrote: > > When you do group by, (the little E on the toolbar) and the group by comes up > > on the query grid, you can change that to Last on your date field. That > > should give you the last date. > . > |