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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SharathConnect With a Mentor Data 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
 
_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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.