Would this bring records back with you have more than one lngTPTID and one of the LastCorrespondence field is null and another one has a value?
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, August 06, 2015 5:27 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Get dataset filter help
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 :
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
Micr osoft 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, 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.
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)
S! ubject: [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
!
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar