?
Solved

Query, return most recent payment and amount

Posted on 2014-04-01
7
Medium Priority
?
268 Views
Last Modified: 2014-04-04
I have a payment file (tblPayFile) in my app.  There can be multiple payments for an account.  I would like to develop a query that returns me the most recent payment, based on pay date for every account along with the date and amount of the payment.  The three pertinent fields in the table are:

Account
PayDate
PayAmt
0
Comment
Question by:mlcktmguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39970960
Try this:

SELECT Account, Max(PayDate) AS MostRecent
FROM tblPayFile
GROUP BY Account

Just create a new query and paste it into the sql.

Flyster
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39970980
That doesn't get the amount of the payment
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39971046
Add it to the select line:

SELECT Account, PayAmt, Max(PayDate) AS MostRecent
FROM tblPayFile
GROUP BY Account
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 39971125
That causes an error, PayAmt has to also be in an aggregate function also.
0
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 900 total points
ID: 39971345
Try this:

SELECT  Account ,
        PayAmount ,
        PayDate
FROM    Account
WHERE   PayDate IN ( SELECT MAX(PayDate)
                     FROM   Account
                     GROUP BY Account )
ORDER BY Account

Open in new window

0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 100 total points
ID: 39971393
No points for this. I believe Sreedhar Vengala gave you the correct solution. Here it is with your table structure:

SELECT tblPayFile.Account, tblPayFile.PayAmt, tblPayFile.PayDate
FROM tblPayFile
WHERE (((tblPayFile.[PayDate]) In (SELECT MAX(PayDate)
                     FROM   tblPayFile
                     GROUP BY Account )))
ORDER BY tblPayFile.Account;

Open in new window

0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39977886
Thanks you
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

777 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