Last row by month, for each 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 tried 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

[June7]

You want the last payment for each month for each customer?

Try:

SELECT CustomerName, Format(PaymentDate,"yyyymm") AS YrMo, Last(Payment) AS LastPay GROUP BY CustomerName, Format(PaymentDate,"yyyymm");

However, Last and First functions can return unexpected results.

Review: http://allenbrowne.com/subquery-01.html#TopN

[htmlasp]

Hey June7,

Yes that is what I'm looking for but one other issue: I need it not to be a read-only query becuase it is feeding into a form that I need users to add data to.

[June7]

You want only the one record for each month for each customer to be retrieved?

Allen's TOP N nested subquery will be an editable recordset. I just tested it. Requires a unique identifier field. I used an autonumber.