query not delivering unique records

[Leaibbs]

I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.

the table structure is:

tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)

tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)

tblAgents
AgentID (PK)
AgentName

tblCustomers
CustID (PK)
CustSurname
Custforename


I have tried the code as below: but it now gives me every payment for the defaulters as opposed to just the last defaulted payment. Can you shed any light on what I have done wrong.

select b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from tblCustomers a, tblPayments b, tblAgents c, tblLoans d
where d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and (DATE()-(SELECT max(PaymentDate) from tblPayments f where f.LoanID=d.LoanID ))>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from 
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

 

[jzwp22]

I'll go through this step by step, so you see how I got to the final UNION query.

I would first create a query to get the loans whose last payments are greater than 7 days from the current date:

query name: qryMaxOfPaymentDateByLoanID

SELECT tblPayments.LoanID, Max(tblPayments.PaymentDate) AS MaxOfPaymentDate
FROM tblPayments
WHERE (((DateDiff("d",paymentdate,Date()))>7))
GROUP BY LoanID;

And I would have a query that has the payment/loan details

query name: qryLoanDetails

SELECT tblCustomers.CustSurname, tblCustomers.CustForeName, tblAgents.AgentName, tblPayments.LoanID, tblPayments.PaymentDate
FROM (tblCustomers INNER JOIN (tblAgents INNER JOIN tblLoans ON tblAgents.AgentID=tblLoans.AgentID) ON tblCustomers.CustID=tblLoans.CustID) INNER JOIN tblPayments ON tblLoans.LoanID=tblPayments.LoanID;

Now join those two queries on the loan # and payment date:

query name: qryOverDue

SELECT qryLoanDetails.CustSurname, qryLoanDetails.CustForeName, qryLoanDetails.AgentName, qryLoanDetails.LoanID, qryLoanDetails.PaymentDate
FROM qryLoanDetails INNER JOIN qryMaxOfPaymentDateByLoanID ON (qryLoanDetails.PaymentDate = qryMaxOfPaymentDateByLoanID.MaxOfPaymentDate) AND (qryLoanDetails.LoanID = qryMaxOfPaymentDateByLoanID.LoanID);

Now get the loans with no payments using this query:

query name: qryLoansWithNoPayments

SELECT tblCustomers.CustSurname, tblCustomers.CustForeName, tblAgents.AgentName, tblLoans.LoanID, "" AS PaymentDate
FROM tblCustomers INNER JOIN (tblAgents INNER JOIN tblLoans ON tblAgents.AgentID = tblLoans.AgentID) ON tblCustomers.CustID = tblLoans.CustID
WHERE tblLoans.LoanID in (SELECT tblLoans.LoanID
FROM tblLoans LEFT JOIN tblPayments ON tblLoans.[LoanID] = tblPayments.[LoanID]
WHERE (((tblPayments.LoanID) Is Null)))


Put the 2 queries together (qryOverDue & qryLoansWithNoPayment) in a UNION query


SELECT tblCustomers.CustSurname, tblCustomers.CustForeName, tblAgents.AgentName, tblLoans.LoanID, "" AS PaymentDate
FROM tblCustomers INNER JOIN (tblAgents INNER JOIN tblLoans ON tblAgents.AgentID = tblLoans.AgentID) ON tblCustomers.CustID = tblLoans.CustID
WHERE tblLoans.LoanID in (SELECT tblLoans.LoanID
FROM tblLoans LEFT JOIN tblPayments ON tblLoans.[LoanID] = tblPayments.[LoanID]
WHERE (((tblPayments.LoanID) Is Null)));

UNION

SELECT qryLoanDetails.CustSurname, qryLoanDetails.CustForeName, qryLoanDetails.AgentName, qryLoanDetails.LoanID, qryLoanDetails.PaymentDate
FROM qryLoanDetails INNER JOIN qryMaxOfPaymentDateByLoanID ON (qryLoanDetails.PaymentDate=qryMaxOfPaymentDateByLoanID.MaxOfPaymentDate) AND (qryLoanDetails.LoanID=qryMaxOfPaymentDateByLoanID.LoanID);