Solved

Query To pull most recent payment and amount

Posted on 2015-01-21
9
102 Views
Last Modified: 2015-01-21
I know I can do this in code but I'm guessing there is a way to do it with a query, or 2.

These are the pertinent fields in my payment table:
ID                              :AutoNumber
TaxRecID                  :Id of the tax record payment is for.  There may be multiple, or no payments for a given tax record.
PaymentDate
PaymentAmount
PostedStatus           :2 = posted.  Posted payments are the only one's to be considered.

For any taxrecid's that have a posted payment, list the most recent posted payment, paymentdate and paymentamount.   At most one payment should be shown for each taxrecid.

If there are no payments for a taxrecID te taxrecID does not have to be in the query results.
0
Comment
Question by:mlcktmguy
  • 5
  • 4
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
select a.TaxRecID, a.PaymentDate, a.PaymentAmount
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
Thank you.  I don't see a qualifier to only include posted payments.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what do you mean? are you referring to PostedStatus?



select a.TaxRecID, a.PaymentDate, a.PaymentAmount, a.PostedStatus
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
Yes, only posted payment should be considered in the selection.  There are also 'NSF' and 'Unposted' payments in the table.  We don't want those included.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
ok. give a detail info about your table. not like this piece by piece of information
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
post a sample expected result coming from your table.
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
The detail example of my table is in the original post:

"These are the pertinent fields in my payment table:
 ID                              :AutoNumber
 TaxRecID                  :Id of the tax record payment is for.  There may be multiple, or no payments for a given tax record.
 PaymentDate
 PaymentAmount
 PostedStatus           :2 = posted.  Posted payments are the only one's to be considered.

 For any taxrecid's that have a posted payment, list the most recent posted payment, paymentdate and paymentamount.   At most one payment should be shown for each taxrecid.

 If there are no payments for a taxrecID te taxrecID does not have to be in the query results. "
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
post the result of this query

select a.TaxRecID, a.PaymentDate, a.PaymentAmount, a.PostedStatus
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
where a.PostedStatus=2
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
Comment Utility
Thank you, works correctly.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now