Solved

Query To pull most recent payment and amount

Posted on 2015-01-21
9
106 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40562935
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
ID: 40562985
Thank you.  I don't see a qualifier to only include posted payments.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563025
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 40563034
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563061
ok. give a detail info about your table. not like this piece by piece of information
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563064
post a sample expected result coming from your table.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40563085
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40563093
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
ID: 40563159
Thank you, works correctly.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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