How to find missing date query

[twhite]

I am trying to create a database that will help me identify if a month is missing based on information that was entered. For example, if I enter in Jan, Feb, Mar, and May for an Employer, can I create a query that "tells" me that April is misssing? I am trying to find the easiest way around this based on the data that is currently in the table. I have the Employer being entered and a month, I just need to know if I am missing a month. The months are entered as 'dates' using January 01, 2010, February 01, 2010, etc..

Thank you in advance for your help!

[weekend00]

Do you also want the missing months like June, July.... as your example?
or only the missing months between two months?

[weekend00]

table1: employer
employerID,employerName,somedate

table2: month12
mon
1
2
3
4
5
6
7
8
9
10
11
12


select a.employerid,a.mon from
(select employerid,mon from employer,month12 group by employerid,mon) as a
left join
employer as b
on a.employerid=b.employerid and a.mon=month(b.somedate)
where b.employerid is null

this query is not tested.

[twhite]

I would like all months up to the current month.

[weekend00]

then put all months from 1 until current month into table month12, not from 1 to 12.

[twhite]

OK here is what I did and what I need:

Table 1 - Employer #

Table 2 - Employer #, months labled (January 2010, February 2010, etc...)

The query is joined on Employer.

I need to find out what months are missing for each Employer

ie; Employer 1 has Jan, Feb, April, May, July.
Employer 2 has Jan, Feb, Mar, May, July.

I need to create a report or query that states Employer 1 is missing Mar and June and Employer 2 is missing April and June.

Does that make sense?

[weekend00]

Did you try my query in #3 floor? remember to change the names of the tables.

[twhite]

I am just not sure on the spot where you want me to have "b.somedate" as I was not populating a month in that table. I only have Employer ID in the first table and then Employer ID and the month in the 2nd table, joined on Employer ID. Do I need to create the months in table one also? Or just a field where it would be populated?

[weekend00]

just create a new table named table12 with only one field MON as number (long integer);

employer table which metioned is your table2, somedate is the month field.

(why didn't post the details of you tables? I just gave a sample in my query, I didn't know what you table names or field names are. you need to modify the query, change the table name and field name according to what table you have.)