An elongated copy query in MS Access

Question

So, I learnt the joys(/s) of INNER JOIN today. Now I need to copy select records from certain specific columns into my main column. Once again, I'll be using the following format:

Please bear in mind that I'm working with 300,000 records. Copy/pasting one by one is not practical.

Tables:
MainTab
CritTab

Key column:
MainTab:- LINK
CritTab:- FORNLINK

Columns to be copied
CritTab.DATE
CritTab.CODE

Criteria
WHERE CritTab.[CODE] = <This kinda code> OR <This other code> AND CritTab.[FORNLINK] = MainTab.[LINK]

So, to clarify: I need to copy specific columns from CritTab to MainTab. As not every record in MainTab will have a corresponding CritTab entry, I cannot simply copy and paste the entire column, as some records won't match up.

Is it possible to do this with a query in Access?

Answer 1

I'm not sure if you need the columns added or not, but if you do run these first. I am changing the column name because you should never use keywords as a column, table or variable name.

ALTER TABLE MainTab ADD COLUMN DATENAME DATETIME
ALTER TABLE MainTab ADD COLUMN CODENAME (WHATEVER DATA TYPE IT IS IN CritTab)

I am moving the JOIN operator from the where clause to the FROM clause. This usually makes it easier to understand how the data is being built and clutters the WHERE a bit less in my opinion. Note: I am doing the same sort of syntax as the article Mike posted in the comments.

UPDATE MainTab INNER JOIN CritTab ON CritTab.[FORNLINK] = MainTab.[LINK]
SET MainTab.DATENAME = CritTab.[Date], MainTab.CODENAME = CritTab.[CODE] 
WHERE CritTab.[CODE] = <This kinda code> OR CritTab.[CODE] = <This other code>