MAX in WHERE clause - ColdFusion, Access

I have two tables, members and payments. I would like to produce a list of all members and their most recent payment information. I've been trying various ways of aggregating with the MAX function, but failing.
Any help would be greatly appreciated.
SELECT tblPayments.ID,memberID,payment_gross,payment_status,txn_id,txn_type,tblMembers.ID 
(SELECT MAX(payment_date) 
FROM tblPayments 
WHERE tblPayments.memberID = tblMembers.ID) as lastPaid 
FROM tblMembers 
ORDER BY lastPaid DESC

Open in new window

GimmejazzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
try this
SELECT p1.id, 
       memberid, 
       payment_gross, 
       payment_status, 
       txn_id, 
       txn_type, 
       m.id 
FROM   tblmembers AS m 
       JOIN tblpayments AS p1 
         ON m.id = p1.memberid 
WHERE  p1.payment_date = (SELECT Max(payment_date) 
                          FROM   tblpayments AS p2 
                          WHERE  p1.id = p2.id)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
(no points)

Is it possible for there to be more than one payment on the same date? If so, you may need to adjust the query to handle that case.
0
ScottMorrisCommented:
Try something like the code below.  I haven't had a chance to test it, but I think it will work.
SELECT memberID, payment_gross, payment_status, txn_id, txn_type, payment_date
FROM tblPayments
INNER JOIN tblMembers ON tblPayments.memberID = tblMembers.ID
INNER JOIN (SELECT tblPayments.ID, MAX(payment_date) FROM tblPayments GROUP BY tblPayments.ID) as LastPaid
ON tblPayments.payment_date = LastPaid.payment_date
ORDER BY payment_date DESC

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

k_murli_krishnaCommented:
In your query you are missing a comma after tblMembers.ID. Also tblPayments.ID in SELECT list will throw an error.

You can also write as:

SELECT top 1 tm.ID,tm.memberID,tm.payment_gross,tm.payment_status,tm.txn_id,tm.txn_type,
tm.ID ,tp.payment_date
FROM tblPayments tp
JOIN tblMembers tm
ON  tp.memberID = tm.ID
ORDER BY tm.lastPaid DESC;

Keep correct column(s) in ORDER BY.

0
k_murli_krishnaCommented:
You can also write as:

SELECT  FIRST(tm.ID),tm.memberID,tm.payment_gross,tm.payment_status,tm.txn_id,tm.txn_type,
tm.ID ,tp.payment_date
FROM tblPayments tp
JOIN tblMembers tm
ON  tp.memberID = tm.ID
GROUP BY tm.memberID,tm.payment_gross,tm.payment_status,tm.txn_id,tm.txn_type,
tm.ID ,tp.payment_date
ORDER BY tm.lastPaid DESC;

You can use FIRST aggregate function on correct column.
The First function returns the first value of column from the result set of a query.
0
Patrick MatthewsCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.