Limit by CUSTOMER and Max Date 1

[ThomasLafferty]

Hi folks! I have a query returning records from a linked table and joining them with 2 tables in my database. The goal is to have a list of clients who have a balance >0 and who are scheduled to billed under the current cycle so that our billers know who they need to charge on a given day.

The billing cycle is determined by either day of the week:
Mon, Tue, Wed etc.

Or by day of the month:
1, 2, 3 etc.

Or else
"As Invoiced"

This part of my query works as expected. What [!]doesn't work[/!] is this: I only want to see one record/row per client, and then only the most recent date. I have tried using Max on the total line of my design grid for the DATE field, but it doesn't seem to give me only 1 record per client.

The problem is that a client may carry a positive balance for several days, or even for several weeks. I don't need to see all of the previous balances, just the most recent, based I would assume on Max date.


Here's what my query returns:

Here's what I would like it to return:

Here's my SQL:

SELECT tblCustomersRoutineBilling.CustomerNumber, [Current Month Aging History].NAME, tblCustomersRoutineBilling.BillingCycle, tblCardDetails.CardType, tblCardDetails.CardNumber, [Current Month Aging History].BALANCE, Max([Current Month Aging History].DATE) AS MaxOfDATE
FROM (tblCustomersRoutineBilling INNER JOIN [Current Month Aging History] ON tblCustomersRoutineBilling.CustomerNumber = [Current Month Aging History].CUSTNO) INNER JOIN tblCardDetails ON tblCustomersRoutineBilling.CustomerNumber = tblCardDetails.CustomerNumber
GROUP BY tblCustomersRoutineBilling.CustomerNumber, [Current Month Aging History].NAME, tblCustomersRoutineBilling.BillingCycle, tblCardDetails.CardType, tblCardDetails.CardNumber, [Current Month Aging History].BALANCE
HAVING (((tblCustomersRoutineBilling.BillingCycle)=WeekdayName(Weekday(Date()),True) Or (tblCustomersRoutineBilling.BillingCycle)=Day(DateAdd("d",-1,Date())) Or (tblCustomersRoutineBilling.BillingCycle)="As Invoiced"))
ORDER BY tblCustomersRoutineBilling.CustomerNumber, [Current Month Aging History].NAME, Max([Current Month Aging History].DATE) DESC;

For what it's worth, here's a shot of my design grid:

Incidentally, I wish the client had not named a field [!]DATE[/!] as this is a dangerous thing to do, but I presently have no control over this field as it is in a table in his database to which I link.

Thanks in advance for any help.

Born once die twice; born twice die once.

 

[PHV]

Typed, untested (SQL code):
SELECT C.CustomerNumber, H.NAME, C.BillingCycle, D.CardType, D.CardNumber, H.BALANCE, H.DATE
FROM ((tblCustomersRoutineBilling AS C
INNER JOIN [Current Month Aging History] AS H ON C.CustomerNumber = H.CUSTNO)
INNER JOIN tblCardDetails AS D ON C.CustomerNumber = D.CustomerNumber)
INNER JOIN (SELECT CUSTNO, Max([Date]) AS MaxOfDATE FROM [Current Month Aging History] GROUP BY CUSTNO
) AS M ON H.CUSTNO = M.CUSTNO AND H.DATE = M.MaxOfDATE
WHERE (C.BillingCycle = WeekdayName(Weekday(Date()),True) OR C.BillingCycle = Day(DateAdd('d',-1,Date())) OR C.BillingCycle = 'As Invoiced')
ORDER BY C.CustomerNumber, H.NAME, H.DATE DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ThomasLafferty]

PHV - You rock! Could you explain why separating Max([Date]) into its own table works the way it does?
BTW - have a star!

Tom

Born once die twice; born twice die once.

 

[PHV]

Another (usually slower) way to help you better understand:
SELECT C.CustomerNumber, H.NAME, C.BillingCycle, D.CardType, D.CardNumber, H.BALANCE, H.DATE
FROM (tblCustomersRoutineBilling AS C
INNER JOIN [Current Month Aging History] AS H ON C.CustomerNumber = H.CUSTNO)
INNER JOIN tblCardDetails AS D ON C.CustomerNumber = D.CustomerNumber
WHERE H.DATE = (SELECT Max([Date]) FROM [Current Month Aging History] WHERE CUSTNO = H.CUSTNO)
AND (C.BillingCycle = WeekdayName(Weekday(Date()),True) OR C.BillingCycle = Day(DateAdd('d',-1,Date())) OR C.BillingCycle = 'As Invoiced')
ORDER BY C.CustomerNumber, H.NAME, H.DATE DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886