Last Row only by Month

[htmlasp]

I have a table set up like:

CUSTOMER ID / CUSTOMER NAME / PAYMENT ID / PAYMENT DATE

Only want to show the last of each payment per month in form based on query.

So if they have a payment in April of $900 on 4/15 and then a payment in May 5/2 $500 and May 15 $1000

It will show two rows...one for April $900 payment and one for May $1000 payment.

I know the logic is something like

If more than one payment row for month, show last, otherwise only one payment for that month show the one payment

HELP PLZ!

I tired just setting hte "last" paramater but it only shows the very last payment, not taking month into account. So it only shows the $1000 may payment. thx

 

[Galaxiom]

SELECT A.CustomerID, A.PaymentID, A.LatestPaymentDate
FROM (
      SELECT CustomerID, Max(PaymentDate) As LatestPaymentDate
      FROM tablename
      GROUP BY CustomerID, Month(PaymentDate)
      ) AS A
RIGHT JOIN tablename AS B
ON A.PaymentID = B.PaymentID;

Always use Max rather than Last. Last does not return what you expect.

BTW. Your table appears to be holding redundent data with both the CustomerID and CustomerNAme.

 

[htmlasp]

k

SELECT A.CustomerID, A.PaymentID, A.LatestPaymentDate
FROM (
      SELECT CustomerID, Max(PaymentDate) As LatestPaymentDate
      FROM tablename
      GROUP BY CustomerID, Month(PaymentDate)
      ) AS A
RIGHT JOIN tablename AS B
ON A.PaymentID = B.PaymentID;

Always use Max rather than Last. Last does not return what you expect.

BTW. Your table appears to be holding redundent data with both the CustomerID and CustomerNAme.

thank you ... but do you know if there are any unorthodox ways to do this where it wont become a read only query? im relying on this query to be the record source for a form where i have users that need to make changes to the table

 

[Galaxiom]

The data that needs to be changed or added should go into a subform.