Transferring data from 1 table to many.

[muzza79]

Hello,

I have to transfer data from one table [all details] into three different ones [personal details], [specific details], [general details] and have two problems.

The first problem is that the information extracted from [all details] and inserted into [personal details] is given a unique id when inserted and i need to retrieve this id to be included in both of the other tables. Unfortunately there is no other way of identifiying an entry uniquely other than this ID, so the only way i can think to do this is as follows -

SELECT (Last.[unique id]) FROM [personal details]

My second problem involves the unique id again, in that if a record is deleted the database will never use the unique id associated with it again. Can i get it to do so?

Up until now i have been trying to solve the first problem by using VBA and SQL but am facing problem after problem. Am i trying to be to complicated, is there an easier way that i am overlooking?

Thanks Muzza

 

[Tim L]

Could you perhaps make this into a two stage process:

1) Tranfer all of the [personal] details from [all details] into [personal details], including the original Id that comes with it, so that you then have the personal details, the new unique Id and the original Id (in addition to any other fields that you may have).

then

2) create the [specific] and [general] details tables, cross-referencing with the original Id but not appending it to the new tables (or deleting the field after creation of the table, so that you can carry out checks first).

?

Not sure of the code or SQL that would be required though.

Tim