Change Select query to Subquery with TOP statement 1

[cloverdog]

Please could anyone help modify a query for a report. Currently the query shows a list of payments due for the coming month by accessing a payments table with a parent of tblContract with a parent of tblCustomer. It shows each client, their bank (.NC field) , PaymentDue and uses the PaymentCleared = NULL to select payments due.

It works well but needs to show, as a separate field, the previous PaymentCleared dateto enable the report to be sorted on the date of last PaymentCleared, which is the best sort order for checking off bank payments received.

Unfortunately this is a level of sophistication beyond my abilities in the dark art of SQL. Any help would be greatly appreciated. The SQL I have is:

SELECT tblNominalCodes.NominalCode, tblPayments2006.PaymentDue, [Surname] & " " & [First Name] AS Name, tblNominalCodes.[Account Name], tblPayments2006.Amount, tblContract.TerminationDate, tblPayments2006.PaymentCleared, tblPayments2006.PaymentType
FROM tblNominalCodes INNER JOIN (tblCustomer INNER JOIN (tblContract INNER JOIN tblPayments2006 ON tblContract.ContractID = tblPayments2006.ContractID) ON tblCustomer.CustomerID = tblContract.CustomerID) ON tblNominalCodes.NominalCode = tblPayments2006.BankNC
WHERE (((tblPayments2006.PaymentDue)<DateAdd("m",1,Now())) AND ((tblContract.TerminationDate) Is Null) AND ((tblPayments2006.PaymentCleared) Is Null) AND ((tblPayments2006.PaymentType)=2))
ORDER BY tblNominalCodes.NominalCode, tblPayments2006.PaymentDue;

Any help in getting this to work would be greatly appreciated. Many Thanks.

 

[dhookom]

The simple solution is to create a totals query that groups by customer (or contract since it isn't clear which last payment) and shows the maximum of the payment date where PaymentCleared is true. Then join this totals query into your existing query and join the customer (or contract) fields.

Duane
Hook'D on Access
MS Access MVP

 

[cloverdog]

Worked a treat. Thank you dhookom