Query Missing Record Entries

[esipp]

I have a table of monthly gross sales reported by retail stores formatted like this

TenantID MonthYear GrossSales

I want to create a query of MISSING RECORDS (i.e. where a month is not reported).

I have also made a table with ALL months.

HOW DO I JOIN these these tables in a query so that I have a column showing ALL MONTHS next to a column of REPORTED MONTHS (some blank) so that I can set the criterion: is null (MonthYear) to get the missing records?

All I can get are complete records and I have tried doing a Left Join without success.

PLEASE HELP!!!

 

[HeyItsManny]

A left join should work. It would be helpful to see the query. Make sure the months table is on the left and if you are pulling data for all the retail stores at once it would seem like you would always have a match unless no stores reported for that month.

 

[HeyItsManny]

You can use two queries to resolve this. The first gets all the matches as you've already done. In the second query use the table with all the months on the left side and do a left join to query 1. Get the gross sales from query 1 and the months from the second query months table.