Finding dates where there is no record

[proben930]

I have a table full of monthly returns for a variety of clients. Each return is due on the 20th of every month. How can i construct a query that will return every month in the last 2 years where there is NOT a return listed in the table?

 

[dcx693]

Here's the SQL for a query that will do what you need:

SELECT YearMonth FROM tblYearMonths WHERE YearMonth NOT IN
(SELECT Format([ReturnDate],"yyyymm") AS ReturnYYYYMM FROM tblReturns);

It assumes:
1- the table with your returns in it is called "tblReturns"
2- the field containing the date of the return is called "ReturnDate"

It requires you to create a separate table called tblYearMonths where you create a field called YearMonth that will contain a listing of all the unique Year/Month combinations you'd like to check. In your case, you want to check the last 2 years. The data in the field needs to look like:
200301
200302
200303
200304
etc....
and the field must be a text field.

If you want a rolling 2-year window, you can change the SQL string to:
SELECT tblYearMonths.YearMonth FROM tblYearMonths
WHERE (((tblYearMonths.YearMonth) Not In (SELECT Format([ReturnDate],"yyyymm") AS ReturnYYYYMM FROM tblReturns) And (tblYearMonths.YearMonth)>Format(DateAdd("m",-24,Date()),"yyyymm")));

 

[dcx693]

Here's an Access 2000 db with some sample data and the query.