Insert records into one table based on distinct months in another table (AC2007) (1 Viewer)

[AOB]

Hi guys,

I have one table of records (table A) which contains a date/time field.

I want to create a query that inserts a new record in another table (table B) for every distinct month that appears in the date/time field in table A (if it doesn't already exist in table B)

I've uploaded a couple of images to hopefully explain what I'm trying to do

Any suggestions?

Thanks

AOB

 

[AOB]

This seems to work...

INSERT INTO tblB
SELECT T.DistinctMonth AS ReferenceMonth
FROM 
    (SELECT DISTINCT DateSerial(Year(A.SampleDateTime),Month(A.SampleDateTime),1) AS DistinctMonth 
     FROM tblA AS A) AS T 
LEFT JOIN tblB AS B ON T.DistinctMonth = B.ReferenceMonth
WHERE B.ReferenceMonth Is Null