append 2 tables with join / relationship (1 Viewer)

[krisleech]

I have tried and tried and now i must try the forum

I have two tables which are linked with an ID field.
In table 1 the ID is an autonumber, in table 2 ID is a number. All records in table 2 that are related to a record in table 1 have the same number in the ID field.

I can append each table on its own to a new table.

But when i append the first table i append all fields accept the ID so a new ID is assigned via autonumber.

The problem is since each record in the first table has a new ID it breaks the link with table 2, as they are joined by the [ID] field.

table 1: (ID = autonum)

[ID] [other fields]

table 2: (ref = autonum)

[ref] [ID] [other fields]

How do i get around this problem and append table 1 and table 2 and table 2's ID field gets updated to reflect the change in ID field in table 1.

If this need further explaining please let me know.

Many thanks Kris.

 

[thouston]

I'm not quite sure why you would want to have these tables both separate and altogether in a new table. There are various ways round your problem but they all really depend on what you are trying to achieve. If you give some more details I'll try to help!

 

[krisleech]

Thanks.

i dont want to combine the two tables into one.
i have two databases, an old version and an new version. The table structures are the same.

I wish to import the data from the old version into the new version.

The two tables i need to import are linked by an ID field.
Table A is the master table and has a unique ID field (autonumber)

Table B has many records to every record in Table One. Eg. all records in table B that have an ID of say 3, link to record ID 3 in table A.

The problem is when i import table A (append query) the ID changes as the ID field can not be imported as it is an autonumber and is re-assigned a new number by the source table.

And then when i import Table B the ID fields no longer match up.
ie. ID = 3 in table B may point to the wrong record in Table A since the ID's in Table A have been re-assigned.

Hope that makes it clearer?

Many thanks Kris.

 

[thouston]

I think your problem is using the Append query to reproduce your tables. Try using either a Make-Table query or Get External Data (from the File menu) to do it instead.

Good Luck!

 

[Pat Hartman]

Use Append query

An append query is the ONLY method you can use to supply a value for an autonumber field. The solution is very simple. Just modify your append query to include the autonumber column from your old table and append it to the autonumber column in your new table. As long as there were no duplicates in your old table, all rows will append with their OLD autonumbers so your foreign keys in the child table will still be valid. Autonumbering in your new table will start at n+1 with n being the value of the highest autonumber that you appended.

 

[krisleech]

append *

To import the data as "*" (ie including the autonumber ID fields) works fine, but there will already be data in the table so that the autonumber fields will have used some numbers already in use so the append will fail for some records.

How do i overcome such a situation?

Many thanks kris.

 

[KevinM]

Does it matter if the new autoIDs are changed in the new table?
i.e. do they have any other relevance other than record identifiers?

If no, try this....

In your NEW Table A create a NEW number field (NOT AUTONUM) called OLDID

In your append query, append your Table A AutoIDs to OLDID.
New AutoIDs will be generated into the AutoID field by Access.

Next Append Query....

For Table B (related records), use NewTable A and Old Table B and join by OLDID field to Table B Foreign Key field.

Set criteria to only include where OLDID Is Not Null.
(the last set of records you appended to A)

Append Table A AutoID to New Table B Foreign Key field.

If you need to repeat this process with a different recordset then remember to set all the OLDID field values to NULL.

Think that should do it.

Try it on a backup first.

 

[krisleech]

Thanks for that ill give it a go today and let you know.
It sounds like it could work. Finger crossed.


Kris.