Rod-
You could do this:
SELECT *
FROM MyTable
WHERE MyTable.LastCorrespondence =
(SELECT Max(LastCorrespondence)
FROM MyTable As T2
WHERE T2.lngTPTID = MyTable.lngTPTID)
OR MyTable.LastCorrespondence IS NULL;
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:29 PM, desertscroller@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thanks John,That works very good. However, there is an issue I will need to resolve. The LastCorrespondence field is not always populated but the record contains data to be reported. I think I may have to create a temporary table to using the id as a key to ensure all ids are included. The users are not allows populating the date fields (correspondence, communications, billing date, payment date).
I appreciate the information. My explanation was not very clear or complete. The four dates are to be used in the filtering of the records if they exist in the records.
Rod
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I appreciate the information. My explanation was not very clear or complete. The four dates are to be used in the filtering of the records if they exist in the records.
Rod
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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@... [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 :That won't work if any of the other fields are different. See my answer.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL 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 (8) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar