Find missing entries

[Molasses26]

I have a table containing meter info and another table that is supposed to
contain a row for each month for each meter in the main table. I need to run
a query to identify the meters that are missing data for a particular month.

Using the data below I want to be able to run a query that tells me that I
am missing the 01/08 MoYr row for MasterMeter 745158. I can't use the
ReadDt because there will sometimes be 2 dates in the same calendar month for
a meter.

Main Table:
MasterMeter Cycle Name Rep
745158 01 KFC K10101
848370 14 IDC K10101
L77959 01 ADOT K10101

Data Table:
MasterMeter ReadDt MoYr Data Comment
L77959 10/31/07 11/07 3100 notmal
L77959 12/03/07 12/07 3256 normal
L77959 01/03/08 01/08 3355 normal
L77959 01/31/08 02/08 3444 normal
L77959 03/03/08 03/08 3521 normal
745158 10/31/07 11/07 5123 notmal
745158 12/03/07 12/07 5456 normal
745158 01/31/08 02/08 5883 normal
745158 03/03/08 03/08 6021 normal
848370 11/16/07 11/07 122 normal
848370 12/18/07 12/07 126 est - snow
848370 01/17/08 01/08 132 normal
848370 02/19/08 02/08 139 normal

 

[Marshall Barton]

I have a table containing meter info and another table that is supposed to
contain a row for each month for each meter in the main table. I need to run
a query to identify the meters that are missing data for a particular month.

Using the data below I want to be able to run a query that tells me that I
am missing the 01/08 MoYr row for MasterMeter 745158. I can't use the
ReadDt because there will sometimes be 2 dates in the same calendar month for
a meter.

Main Table:
MasterMeter Cycle Name Rep
745158 01 KFC K10101
848370 14 IDC K10101
L77959 01 ADOT K10101

Data Table:
MasterMeter ReadDt MoYr Data Comment
L77959 10/31/07 11/07 3100 notmal
L77959 12/03/07 12/07 3256 normal
L77959 01/03/08 01/08 3355 normal
L77959 01/31/08 02/08 3444 normal
L77959 03/03/08 03/08 3521 normal
745158 10/31/07 11/07 5123 notmal
745158 12/03/07 12/07 5456 normal
745158 01/31/08 02/08 5883 normal
745158 03/03/08 03/08 6021 normal
848370 11/16/07 11/07 122 normal
848370 12/18/07 12/07 126 est - snow
848370 01/17/08 01/08 132 normal
848370 02/19/08 02/08 139 normal

To find missing entries, the query needs to know which
entries are supposed to be there. This is normally done by
using another table (named MonthYear) with one date field
(named MonYr) and populated with all possible month/year
combinations. Let's say the table has records:
. . .
9/1/2007
10/1/2007
11/1/2007
12/1/2007
1/1/2008
2/1/2008
. . .

Then you can use a query (named AllMonYr) that's something
like:

SELECT M.MasterMeter, Y.MonYr
FROM [Main Table] As M, MonthYear As Y
WHERE Month(Y.MonYr) >= Month([enter start date])
And Year(Y.MonYr) >= Year([enter start date])
And Month(Y.MonYr) <= Month([enter end date])
And Year(Y.MonYr) <= Year([enter end date])

to build a list of all possible combinations.

Now you can get the desired result from a query like:

SELECT A.MasterMeter, A.MonYr
FROM AllMonYr As A LEFT JOIN [Data Table] As D
ON A.MasterMeter = D.MasterMeter
And Month(MonYr) = Month(D.ReadDt)
And Year(MonYr) = Year(D.ReadDt)
And Month(MonYr) = Month(D.ReadDt)
And Year(MonYr) = Year(D.ReadDt)
WHERE D.MasterMeter Is Null