Limit records returned by a Totals query according to Max field 1

[PSchubert]

Hello, and thanks in advance...

I need to include only the payment amount that corresponds to the max of the payment date field.

Here's what I have so far that's relevant:

<b><u>tblInvoices</u></b>
nfldidxClients
dfldPymtDate
cfldPymtAmount

<b><u>qryLatestAcctPayments</u></b>
<i>(A Totals query, using <b>tblInvoices</b>)</i>
Field: dfldPymtDate
Table: tblInvoices
Total: Max
Criteria: Is Not Null

Field: nfldidxClients
Table: tblInvoices
Total: Group By

<b><u>qrysrpt90Plus</u></b>
<i>(A Select query, using <b>qryLatestAcctPayments</b> and <b>tblInvoices</b> (among others), which returns records where the last payment was made more than 90 days ago)</i>
Field: MaxOfdfldPymtDate
Table: qryLatestAcctPayments
Criteria: <i>It works, omitted for simplicity's sake</i>

Field: nfldidxClients
Table: tblInvoices

So far, so good. When run, <b>qrysrpt90Plus</b> returns the desired records. However, when I add the field <b>tblInvoices.cfldPymtAmount</b> to try to return the most recent payment amount (the payment made on the date that corresponds to <i>MaxOfdfldPaymentDate</i>), I get all of the payments made on all of the dates. How can I get only the payment amount made on the most recent date?

 

[Remou]

Do you want .. ?

SELECT t.nfldidxClients, 
       t.dfldPymtDate,
       t.cfldPymtAmount,
FROM tblInvoices
INNER JOIN (SELECT nfldidxClients, Max(dfldPymtDate) As MaxDate
            FROM tblInvoices tm
            GROUP BY nfldidxClients
            HAVING dfldPymtDate<Date()-90) tm
ON t.nfldidxClients=tm.nfldidxClients
AND t.dfldPymtDate=tm.MaxDate

 

[PSchubert]

Thank you for your response, Remou. I was unable to successfully duplicate your SQL. Here is my SQL of qrysrpt90Plus, which returns the latest balance, the latest invoice date, the most recent payment date, nfldidxClients, and the account number:

<code>
SELECT qryLatestAcctBalances.cfldBalance,
tblInvoices.dfldDate,
qryLatestAcctPayments.MaxOfdfldPymtDate,
tblInvoices.nfldidxClients,
tblClients.nfldAccountNum
FROM tblRatesTerm, tblClients
INNER JOIN ((qryLatestAcctBalances INNER JOIN qryLatestAcctPayments
ON qryLatestAcctBalances.nfldidxClients = qryLatestAcctPayments.nfldidxClients)
INNER JOIN tblInvoices
ON qryLatestAcctBalances.nfldidxClients = tblInvoices.nfldidxClients)
ON tblClients.idxClients = tblInvoices.nfldidxClients
WHERE (((qryLatestAcctBalances.cfldBalance)>0)
AND ((qryLatestAcctPayments.MaxOfdfldPymtDate)>DateAdd("d",-90,DateAdd("d",[tblRatesTerm].[nfldPaymentTerm],[tblInvoices].[dfldDate]))));
</code>

All I want to add is the payment amount (tblInvoices.cfldPymtAmount) that has the same date (tblInvoices.dfldPymtDate) as qryLatestAcctPayments.MaxOfdfldPymtDate

Thanks again for your help! (I don't know why the TGML isn't working)

 

[PSchubert]

OK, got it. The solution, hinted at by [green]Remou[/green], was to create another query to return only the payment amount made on the date returned by qryLatestAcctPayments.MaxOfdfldPymtDate, and to include that new query in qrysrpt90Plus. I changed the name of qryLatestAcctPayments to qryLatestPaymentDate, and named the new query qryLatestPaymentAmt. Here is the SQL for the working queries, if anyone is interested:

[b][u]qryLatestAcctBalances[/u][/b]
SELECT Max(tblInvoices.dfldDate) AS MaxOfdfldDate, tblInvoices.nfldidxClients, tblInvoices.nfldInvoiceNumber, tblInvoicePrevBalance.cfldBalance
FROM tblInvoices 
INNER JOIN tblInvoicePrevBalance 
   ON tblInvoices.nfldInvoiceNumber = tblInvoicePrevBalance.nfldInvoiceNumber
WHERE (((tblInvoicePrevBalance.cfldBalance)>0))
GROUP BY tblInvoices.nfldidxClients, tblInvoices.nfldInvoiceNumber, tblInvoicePrevBalance.cfldBalance;

[b][u]qryLatestPaymentDate[/u][/b]
SELECT Max(tblInvoices.dfldPymtDate) AS MaxOfdfldPymtDate, tblInvoices.nfldidxClients
FROM tblInvoices
GROUP BY tblInvoices.nfldidxClients
HAVING (((Max(tblInvoices.dfldPymtDate)) Is Not Null));

[b][u]qryLatestPaymentAmt[/u][/b]
SELECT qryLatestPaymentDate.nfldidxClients, qryLatestPaymentDate.MaxOfdfldPymtDate, tblInvoices.cfldPymtAmount
FROM qryLatestPaymentDate 
INNER JOIN tblInvoices 
   ON (qryLatestPaymentDate.nfldidxClients = tblInvoices.nfldidxClients) 
   AND (qryLatestPaymentDate.MaxOfdfldPymtDate = tblInvoices.dfldPymtDate);

[b][u]qrysrpt90Plus[/u][/b]
SELECT qryLatestAcctBalances.cfldBalance, tblInvoices.dfldDate, qryLatestPaymentDate.MaxOfdfldPymtDate, tblInvoices.nfldidxClients, tblClients.nfldAccountNum, IIf([tfldCompany] Is Null,[tfldFirstName] & " " & [tfldLastName],[tfldCompany]) AS AccountName, qryLatestPaymentAmt.cfldPymtAmount
FROM tblRatesTerm, qryLatestPaymentAmt, tblClients 
INNER JOIN ((qryLatestAcctBalances 
   INNER JOIN  qryLatestPaymentDate 
      ON qryLatestAcctBalances.nfldidxClients = qryLatestPaymentDate.nfldidxClients) 
         INNER JOIN tblInvoices 
            ON qryLatestAcctBalances.nfldidxClients = tblInvoices.nfldidxClients) 
   ON tblClients.idxClients = tblInvoices.nfldidxClients
WHERE (((qryLatestAcctBalances.cfldBalance)>0) AND ((qryLatestPaymentDate.MaxOfdfldPymtDate)>DateAdd("d",-90,DateAdd("d",[tblRatesTerm].[nfldPaymentTerm],[tblInvoices].[dfldDate]))));

Parenthetically, TGML does work, if I use square brackets instead of angle brackets, DUH!