SQL Statement

[proben930]

help! troubleshoot this SQL statement

SELECT tblYearMonths.YearMonth, [Client Data].[Client Number]
FROM [Client Data], tblYearMonths
WHERE ((([Client Data].[Beginning Date])<[dateapplicable]) AND ((tblYearMonths.DateApplicable)<Date()) AND Not In (SELECT Format([due date],"yyyymm") AS ReturnYYYYMM, [monthly returns].[Client Number] FROM [monthly returns]))
;

 

[proben930]

Allow me to clarify how i got it and what its supposed to do--

This query:
SELECT Format([due date],"yyyymm") AS ReturnYYYYMM, [monthly returns].[Client Number]
FROM [monthly returns];

returns all the "yearmonths" where a return was filed.

This query:
SELECT tblYearMonths.YearMonth, [Client Data].[Client Number]
FROM [Client Data], tblYearMonths
WHERE ((([Client Data].[Beginning Date])<[dateapplicable]) AND ((tblYearMonths.DateApplicable)<Date()));

Returns all the "yearmonths" where a return SHOULD have been filed


I wanted a query to return the "yearmonths" where there should have been one filed but there wasn't. a year month is a table that has 199901,199902, etc.. all the way through 200601.

 

[Brianwarnock]

SELECT tblYearMonths.YearMonth, [Client Data].[Client Number]
FROM [Client Data], tblYearMonths
WHERE ((([Client Data].[Beginning Date])<[dateapplicable]) AND ((tblYearMonths.DateApplicable)<Date()) AND Not In (SELECT Format([due date],"yyyymm") AS ReturnYYYYMM, [monthly returns].[Client Number] FROM [monthly returns]))
;

OK let's stick my neck out since nobody else has replied, I don't think that you can do what you want to do witha sub query.
As I see it you have 2 queries returning custid and a date yyyymm.
One gives all the dates than can appear the other the dates that have, and you now require the missing dates for each custid.

You need a query left joining custid and dates (the query with all dates is the left) and criteria for date from right query is null.

something like
SELECT queryall.custid, queryall.date, qryfiled.date
FROM queryall LEFT JOIN qryfiled ON (queryall.custid = qryfiled.custid) AND (queryall.date = qryfiled.date)
WHERE (((qryfiled.date) Is Null));

Brian