You have to enter the pkfield value either manually or with an update query.
I want to split this table and create a second table named table2 as follow:
table2 will have the fields: pkfield, field2, field
5 <<-- I think you meant
field4 not
field5
so table1 will look like this:
table1 will have the fields: pkID, field1, field3, field5, fkfield
Here is another way. DO THIS ON A COPY OF THE DB....just in case......you have been warned
1) Create a copy of table1. Copy the structure and data. Save as table2.
2) Edit table 2 - delete the field fkfield and save the table.
Now you have two tables with identical data in the records, but table 2 does not have the field fkfield.
3) Create a new query and paste in this SQL:
Code:
UPDATE Table2, Table1 SET Table1.fkfield = [Table2].[pkId]
WHERE (((Table2.field2)=[Table1].[field2]) AND ((Table2.field4)=[Table1].[field4]));
4) Execute the query. You will get a warning about updating X number of records. Click YES.
Open table1. Look at the field fkfield. All records should have a value for this field.
Close table1.
5) Open table2 in design view. Delete the fields field1, field3 and field5. Close and save the table.
6) Open table1 in design view. Delete the fields field2 and field4. Close and save the table.
Done!!??
You can create another query and past in this SQL
Code:
SELECT Table2.pkId, Table1.fkfield, Table1.field1, Table2.field2, Table1.field3, Table2.field4, Table1.field5
FROM Table2 INNER JOIN Table1 ON Table2.pkId = Table1.fkfield;
Should return the original table 1 table data PLUS fkfield had values.