Carry over last sequenced number

[Musa via AccessMonster.com]

Hello,

I have
Table1 PK Survey_ID
Table2 PK Survey_ID


I would like to take the last entry of a sequence number and carry it over to
a new record in another table based on the primary key. For Example:

Table1 Survey ID Seq_1 Table 2 Survey ID Seq_1
1 1 1
3 (new Record)
1 2 2
1 " "
1 3 3
2 " "
2 1
3 1
3 2

 

[Musa via AccessMonster.com]

The data should look like this

Table1 SurveyID Seq1 Table 2 SurveyID Seq1 (on newrecord)
1 1 1 3
1 2 - -
1 3 - -
2 1 2 1
3 1 3 2
3 2 - -