[MS_AccessPros] Re: Get dataset filter help

 

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 :

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 

http://www.viescas.com/ 

(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, 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 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. 

7ony

Sent from my Verizon Wireless 4G LTE smartphone

-------- Original message --------

From: "desertscroller@... [MS_Access_Professionals]"

Date:08/04/2015 11:24 PM (GMT-05:00)

To: MS_Access_Professionals@yahoogroups.com

Subject: [MS_AccessPros] Get dataset filter help

 

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

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: desertscroller@cox.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___