Rod-
Did you not see my earlier answer?
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
(Paris, France)
On Aug 6, 2015, at 1:06 AM, desertscroller@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I understand what you have show but I did not make my request clear. What I am trying to do is, filter all the records from the record set but only include the first record for any give lngTPTID.
Rod
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Rod
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
That won't work if any of the other fields are different. See my answer.
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
(Paris, France)
On Aug 5, 2015, at 10:14 AM, Tony V Meece mr_7ony@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Drag down your TPTID & CORRESPONDENCE DATE fields.Use the Sigma on your toolbar for a 'summary' type query. Set the date field to use MAX instead of group.7onySent from my Verizon Wireless 4G LTE smartphone-------- Original message --------From: "desertscroller@... [MS_Access_Professionals]"Date:08/04/2015 11:24 PM (GMT-05:00)Subject: [MS_AccessPros] Get dataset filter helpI 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
AuditorName strTaxPayerName lngTPTID Research Date LastCorrespondence Bedsun, Robert AZ II LLC 25193 1/28/2014 8/3/2015 Bedsun, Robert AZ II LLC 25193 1/28/2014 7/23/2015
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar