How can I find missed years

[3587ch]

I have a tblpayments with a clientid and payment date, payments are made yearly (sometimes). I need a way to findout for each client which years they missed. How can I do this?

So I might say between 2005 and 2009 which clients missed a payment and then I need to know their clientID and the year(s) they missed?

Thank you,

 

[MajP]

One way is to make a table of year values.

tblYears
numYears (numeric value)

numYear
2004
2005
2006
2007
2008
2009
2010
2011
2012

Populate the table with the minimum year your data will hold, and go out as far as you think necessary.

Now build a query that returns the range you want from tblYears
qryYearRange

SELECT tblYears.numYear
FROM tblYears
WHERE tblYears.numYear >=[Enter Start Year] And  tblYears.numYear<=[Enter End Year]

Now build a cartesian product of the all the clients and the years in question
qryCart_YearsAndClients

SELECT tblClients.clientID, qryYearRange.numYear
FROM qryYearRange, tblClients;

Now build a query that changes the date in the payment table into a numeric year
qryYearPayment

SELECT tblPayments.clientID, CLng(Year([dtmPaymentDate])) 
AS YearPayment
FROM tblPayments
ORDER BY tblPayments.clientID, CLng(Year([dtmPaymentDate]));

Now build a query that returns all missing years

SELECT 
 clientID, 
 numYear
FROM qryCart_YearsAndClients 
LEFT JOIN qryYearPayments ON   
 (qryCart_YearsAndClients.clientID =
  qryYearPayments.clientID) AND
 (qryCart_YearsAndClients.numYear =  
 qryYearPayments.YearPayment)
WHERE 
 qryYearPayments.clientID Is Null
ORDER BY 
 qryCart_YearsAndClients.clientID,
 qryCart_YearsAndClients.numYear;

Given this

clientID	dtmPaymentDate
1	1/1/2005
1	1/1/2006
1	1/1/2008
1	1/1/2009
2	1/1/2006
2	1/1/2007
2	1/1/2009

the query produces this if I enter 2005 and 2009

clientID	numYear
1	2007
2	2005
2	2008

This could all be done in a single query, but it would be hard to explain so I used a bunch of small queries.