Query to Find a Missing Record

[KCC47]

I am trying to create a query that will find missing records in a series of dates.

There is a tax return for each month for each hotel, but I need to find where the hotel failed to submit a return. There will not be any records in the table in this situation.

The test data in the attached Db has a return for January and March, but February is missing. Any suggestions for a query that will point this out?

[June7]

Maybe:

SELECT Hotel_Account, [Year] FROM Return_Tbl WHERE Count([Month])<12 GROUP BY Hotel_Account, [Year];

or

SELECT Hotel_Account FROM Return_Tbl WHERE Count([Month])<[enter months to date] AND [Year]=[enter year to search] GROUP BY Hotel_Account;

Otherwise, create a table of all months (that's 12 records) and try a Find Unmatched query. Apply filter for the year to search.

Month and Year are reserved words and should avoid reserved words in naming convention.

[KCC47]

Do you mean create a table of month names only? There would be a primary key set to auto-number, and another field for month names.

How does one create the find unmatched query in this situation.

This is across multiple years, would you apply the filter a year at a time?

[June7]

I edited my previous post for additional suggestions.

An autonumber primary key in the months table is irrelevant.

If you want to find gaps in multiple years using Find Unmatched, that would require a table of all year/month combinations. One way to create that dataset is to create a table of months and a table of years. Then create a query pulling both tables in a Cartesian relation. This will provide all year/month combinations. Other way is to directly build YrMon table (copy/paste can be useful).

SELECT YearsTbl.[Yr], MonthsTbl.[Mon] FROM MonthsTbl, YearsTbl;

[KCC47]

I'm afraid I am struggling a bit on this one.

I've created a table for all month and year combinations I'm concerned about (January 2010 thru April 2014), and its called Calendar_Tbl.

I've also created a find unmatched query, but I don't think its working as designed.

I'm needing it to find missing months by account. Each hotel should have one return per month, but I need to know when one has missed a month. This unmatched query is returning information without knowing which account is being referenced.

[lfpm062010]

Modified slightly based on what June7's suggestion.

Build a query "qry_Calendar_Tbl" to contain all month and year combo for year 2013 and 2014. If need more month and year then you have to add year to YearTbl.

Build a query "qry_Missing_Tax" to find missing month year (for year 2013 and 2014).

See the example,
Hotel_Accounts_Test_Db.zip

[KCC47]

Modified slightly based on what June7's suggestion.

Build a query "qry_Calendar_Tbl" to contain all month and year combo for year 2013 and 2014. If need more month and year then you have to add year to YearTbl.

Build a query "qry_Missing_Tax" to find missing month year (for year 2013 and 2014).

See the example,
Hotel_Accounts_Test_Db.zip

This works in a sense. It will show me that I have yet to receive any returns for the April - December of this year. I need to know if an individual hotel missed filing a return for March 2013, for example. Every single other hotel may have, but one may not have. Does this make sense?

[lfpm062010]

I have only use your example to show you that it will be able to show the missing month and year combo.

I have re-did the queries and maybe this will be more close to what you are looking for.

Hotel_Accounts_Test_Db.zip

[KCC47]

I have only use your example to show you that it will be able to show the missing month and year combo.

I have re-did the queries and maybe this will be more close to what you are looking for.

Hotel_Accounts_Test_Db.zip

This seems to have done the trick. Thank you both so much!