ACCESS FEED: Re: [MS_AccessPros] Get dataset filter help

Rabu, 05 Agustus 2015

Re: [MS_AccessPros] Get dataset filter help

Rod-

Like this:

SELECT *

FROM MyTable

WHERE MyTable.LastCorrespondence =

(SELECT Max(LastCorrespondence)

FROM MyTable As T2

WHERE T2.lngTPTID = MyTable.lngTPTID)

Substitute the name of your table everywhere I used "MyTable".

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 5, 2015, at 5:24 AM, desertscroller@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query that returns a set of data that includes multiples of an id. The records contain the same kind of data but the dates will vary. What I am trying to do is filter the data to include only one record per id with the most recent dates. I have a query that groups all records based on the id with the most recent dates first but can not seem to filter the data set to a subset that only includes the record for each id with the most recent dates. (This will be used to generate a status report of id activity. Example: The two excerpts from the data set but only want to have only the first record for each lngTPTID in new data set.

Any suggestions would be a great help.
Rod


AuditorName	strTaxPayerName	lngTPTID	Research Date	LastCorrespondence
Bedsun, Robert	VCCA GILBERT INVESTORS LLC	24588	9/15/2013	8/3/2015
Bedsun, Robert	VCCA GILBERT INVESTORS LLC	24588	9/15/2013	7/22/2015
Bedsun, Robert	VCCA GILBERT INVESTORS LLC	24588	9/15/2013	9/30/2013