Left Join not

[j_a_monk]

I am trying to run a full join on 2 tables saving into a 3rd table joining on 3 fields.

I have gotten all records to load from Video 2 and gotten only records that have a match to load from Video2 but I have not been able to get matching records to join.

Is there something I am missing in this?

INSERT INTO HDDandVideo2 (watched, discType, discNum, discLabel, videoPath, hashMD5, fileType, fileSizeMB, title, writeDateTime, videoFormat, width, height, bpp, frames, fps, audioFormat, frequency, bpc, channels, length, category)

SELECT Video2.watched, Video2.discType, Video2.discNum, Video2.discLabel, Video2.path, Video2.title, Video2.fileType, Video2.fileSizeMB, Video2.writeDateTime, Video2.videoFormat, Video2.width, Video2.height, Video2.bpp, Video2.frames, Video2.fps, Video2.audioFormat, Video2.frequency, Video2.bpc, Video2.channels, Video2.length, Video2.hashMD5, Video2.category

FROM Video2 LEFT JOIN HDDandVideo2 
ON (Video2.fileSizeMB = HDDandVideo2.fileSizeMB) 
AND (Video2.fileType = HDDandVideo2.fileType) 
AND (Video2.hashMD5 = HDDandVideo2.hashMD5)

WHERE (((Video2.hashMD5)=[HDDandVideo2].[hashMD5]) AND ((Video2.fileType)=[HDDandVideo2].[fileType]) AND ((Video2.fileSizeMB)=[HDDandVideo2].[fileSizeMB]));

I have tried Googling but have not found anythign that fits.
Using Access 2007.

Thank you

[June7]

Why would you be joining to the destination table to insert new records? Are you really trying to update existing records? That would be an UPDATE not INSERT sql and the syntax for the sql is very different.

[j_a_monk]

I will be updating the destination table in the future with new records from both source tables.

[June7]

Okay but I still don't understand why the destination table is in the nested SELECT query. You say 3 tables are involved but I see only 2 table names.

[j_a_monk]

Sorry, I have 2 source tables both of which will also be updated in the future with new records, those are Video2 and HDDVideo. The destination table I want to join to is HDDandVideo2.

If there is a way to import directly into HDDandVideo2 and join on import?

[j_a_monk]

I ended up joining the two source tables, so it is solved for now. I was trying to keep them separate but this is managable.

Sorry for the confusion, thank you