show the most recent payment

[Andy79]

Hi,

I am trying to write a query to return the most recent payment date
and how much was paid.

The database has two tables, members and payments. Any one member can
have any number of payments..

Here is where I got so far

SELECT members.memberID, payments.paymentsID,
max(payments.paymentdate) AS 'Last Payment', payments.paymentvalue

FROM members INNER JOIN payments ON members.memberID =
payments.paymentID

Where ...

And my problem is how to write the Where that will only show the
payment value from the same row as the 'max' paymentdate (ie the most
recent date a payment was made)

Because the system is being used for current payments and to record
old payments the ordering of paymentID and paymentdate doesn't match
(people are transfering old payments onto the system from paper
records) so I can't use 'select first(payments.pa...'.

Any clues as to how to write the cirteia to 'show me the payment value
from the row with the most recent date'

Thanks for your help!

Regards
Andy

 

[Andy79]

Your example does not have a need for the members table so I
reduced the query to:

SELECT payments.paymentsID,
                payments.paymentdate,
                payments.paymentvalue
FROM payments
INNER JOIN
     (SELECT X.paymentsID,
                    Max(X.paymentdate) As LatestDate
      FROM payments As X
      GROUP BY X.paymentsID
     ) As LatestPayment
ON payments.memberID = LatestPayment.paymentID
   And payments.paymentdate = LatestPayment.LatestDate

Thanks, I tried the query (pasted below) however no results were
returned? I cant think why though..?

SELECT payments2.paymentID,
payments2.paymentdate,
payments2.paymentvalue
FROM payments2
INNER JOIN
(SELECT X.paymentID,
Max(X.paymentdate) As LatestDate
FROM payments2 As X
GROUP BY X.paymentID
) As LatestPayment
ON payments2.memberID = LatestPayment.paymentID
And payments2.paymentdate = LatestPayment.LatestDate;

Regards
Andy

 

[Lou]

Thanks, I tried the query (pasted below) however no results were
returned? I cant think why though..?

SELECT payments2.paymentID,
               payments2.paymentdate,
               payments2.paymentvalue
FROM payments2
INNER JOIN
    (SELECT X.paymentID,
                   Max(X.paymentdate) As LatestDate
     FROM payments2 As X
     GROUP BY X.paymentID
    ) As LatestPayment
ON payments2.memberID = LatestPayment.paymentID
  And payments2.paymentdate = LatestPayment.LatestDate;

Looks valid to me.  Double check that payments2 actually has
records and that each record has a paymentdate.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Your initial query implies that for each members.memberID there is one
or more corresponding payments.paymentsID rows.

If that's true, there would be output from this query:

SELECT members.memberID, payments.paymentsdate
FROM members inner join payments on members.memberID =
payments.paymentID

If that works, try adding the where clause as follows:

SELECT members.memberID, payments.paymentdate
FROM members inner join payments on members.memberID =
payments.paymentID
WHERE payments.paymentdate = ( select max( paymentdate )
from payments
where paymentID = members.memberID )

This won't work if the paymentdates are null, so check your data
before continuing.

Marsh's query is along the same lines, but somehow Access didn't choke
when the column "payments2.memberID" was introduced. Is it possible
there is a memberID in the payments table?

 

[Andy79]

Thanks for the continued help;

I get a return with this query using the updated ON clause..

SELECT payments2.paymentID, payments2.paymentdate,
payments2.paymentvalue
FROM payments2 INNER JOIN (SELECT X.paymentID,
Max(X.paymentdate) As LatestDate
FROM payments2 As X
GROUP BY X.paymentID
) AS LatestPayment ON (payments2.paymentdate =
LatestPayment.LatestDate) AND (payments2.paymentID =
LatestPayment.paymentID)

However, this is showing me ALL the paymentIDs.. when I only want to
see the paymentID from the most recent payment for each member??

I am not sure what to edit to correct this...

Thanks again
Andy

 

[Andy79]

Thanks for the continued help;

I get a return with this query using the updated ON clause..

SELECT payments2.paymentID, payments2.paymentdate,
payments2.paymentvalue
FROM payments2 INNER JOIN (SELECT X.paymentID,
                    Max(X.paymentdate) As LatestDate
      FROM payments2 As X
      GROUP BY X.paymentID
     )  AS LatestPayment ON (payments2.paymentdate =
LatestPayment.LatestDate) AND (payments2.paymentID =
LatestPayment.paymentID)

However, this is showing me ALL the paymentIDs.. when I only want to
see the paymentID from the most recent payment for each member??

I am not sure what to edit to correct this...

Thanks again
Andy

Big thanks to you both, a small edit to Lou's code has done the
trick.. here is the final working query, showing me the membersID,
date of last payment and the value of the last payment:

SELECT MembersList.MemberID, Max(Payments2.PaymentDate) AS
MaxOfPaymentDate, Payments2.PaymentValue
FROM MembersList INNER JOIN Payments2 ON MembersList.MemberID =
Payments2.MemberID
WHERE payments2.paymentdate = ( select max( paymentdate )
from payments2
where memberID =
memberslist.memberID )
GROUP BY MembersList.MemberID, Payments2.PaymentValue;

Regards
Andy

 

[Andy79]

Big thanks to you both, a small edit to Lou's code has done the
trick.. here is the final working query, showing me the membersID,
date of last payment and the value of the last payment:

SELECT MembersList.MemberID, Max(Payments2.PaymentDate) AS
MaxOfPaymentDate, Payments2.PaymentValue
FROM MembersList INNER JOIN Payments2 ON MembersList.MemberID =
Payments2.MemberID
WHERE payments2.paymentdate = ( select max( paymentdate )
                                from payments2
                                where memberID =
memberslist.memberID )
GROUP BY MembersList.MemberID, Payments2.PaymentValue;

Regards
Andy

Here is the whole jolly lot, showing a bunch of stuff, the latest
payment dates/values and the sum of all payments..

SELECT MembersList.MemberNumber, MembersList.Title1,
MembersList.Initials1, MembersList.FirstName1, MembersList.LastName1,
MembersList.Title2, MembersList.Initials2, MembersList.FirstName2,
MembersList.LastName2, MembersList.LetterHead, MembersList.Address1,
MembersList.Address2, MembersList.Town, MembersList.County,
MembersList.PostCode, MembersList.Country, MembersList.Email,
MembersList.Phone, Branches.BranchName, MemberType.MemberType,
MembersList.MembershipStarted, MembersList.PreferredPaymentMethod,
MembersList.SubscriptionDate, MembersList.GiftAidStatus,
Max(Payments2.PaymentDate) AS [Last Payments], Payments2.PaymentValue
AS [Last Payment Value], Sum(Payments2_1.PaymentValue) AS [Sum OF
Payments]
FROM (MemberType INNER JOIN ((Branches INNER JOIN MembersList ON
Branches.BranchID = MembersList.Branch) INNER JOIN Payments2 ON
MembersList.MemberID = Payments2.MemberID) ON MemberType.MemberTypeID
= MembersList.MemberType) INNER JOIN Payments2 AS Payments2_1 ON
MembersList.MemberID = Payments2_1.MemberID
WHERE (((Payments2.paymentdate)=(select
max( paymentdate ) from
payments2 where memberID =
memberslist.memberID )))
GROUP BY MembersList.MemberNumber, MembersList.Title1,
MembersList.Initials1, MembersList.FirstName1, MembersList.LastName1,
MembersList.Title2, MembersList.Initials2, MembersList.FirstName2,
MembersList.LastName2, MembersList.LetterHead, MembersList.Address1,
MembersList.Address2, MembersList.Town, MembersList.County,
MembersList.PostCode, MembersList.Country, MembersList.Email,
MembersList.Phone, Branches.BranchName, MemberType.MemberType,
MembersList.MembershipStarted, MembersList.PreferredPaymentMethod,
MembersList.SubscriptionDate, MembersList.GiftAidStatus,
Payments2.PaymentValue, MembersList.MembershipEnded
HAVING (((MembersList.MembershipEnded) Is Null));