I have 2 tables and each table has a unique ID that is not auto numbered (no idea why they did this).

Table1 has a Primary ID Column and Table2 has its own Primary ID and a Foreign ID that correlates to Table1 Primary ID. These ID's are used in a relationship.

My goal is to add a new record to Table1 one using the next highest ID and do the same with Table2 Primary ID. Now I need to update Table2 Primary ID with the PrimaryID from Table1.

I used a select Max(Field1) From Table1 and Table2 to get the next Primary ID's but cannot figure out how to get that into Table2 Foreign ID.

Something like;

Update Table2 Set Field2 = "here is where I get lost" In (SELECT Max(Field1) From Table1.

I tried it all sorts of ways and no luck some help would be appreciated.