Update Field based on field in another table

[l2evenge]

I have one table that has repeating dates as below, though the HOLIDAY_KEY field is not fully populated yet(there are over 9000 records and not all of them are holidays). Each date will be represented continuously.

CAL_DATE	DATA	HOLIDAY_KEY
1/1/2011	11	1
1/1/2011	1000	1
1/15/2011	12	2
1/15/2011	899	2

<tbody>
</tbody>

Then I have a table as below. Which only calls out the holiday dates themselves(as we are tagging them).

ACT_DATE	HOLIDAY_KEY
1/1/2011	1
1/15/2011	2

<tbody>
</tbody>

Is there a way for me to update the holiday key in the first table automatically based on the dates in the second table?

 

[TerryHogarth21]

Should be a simple update query

 UPDATE Calendar INNER JOIN Hol_Day ON Calendar.CAL_DATE = Hol_Day.ACT_DATE SET Calendar.HOLIDAY_KEY = [Hol_Day].[HOLIDAY_KEY];

Replace Calendar(the one that has blank Holiday_Key and Hol_Day with your respective tables.)

Unless you mean update it every time you add a record in the first table - you automatically want the Holiday_Key to populate?

 

[l2evenge]

Awesome! Took me a minute to figure out where to put the code. I am an Access newb.

Thank you so much Terry!