query not delivering unique records 1

[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;

 

[SkipVought]

Hi,

Use DISTINCT and ZERO for the date in the second query as DateTime values are NUMBERS...

select DISTINCT 
  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 DISTINCT
  0 
, 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;

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[LeaIbbs]

Thanks Skip, I have tried this but still get the same result


it still gives me every payment for the defaulters as opposed to just the last defaulted payment per customer.

 

[SkipVought]

Your criteria does not define the MAX date...

select DISTINCT 
  MAX(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

Group By 
  a.CustSurname
, a.Custforename
, c.AgentName
, d.LoanID
....

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}