Relocation of IDs when merging two tables (1 Viewer)

Dezirous

Rafi Ahmed

Local time

Today, 23:41

Joined

Jul 8, 2009

Messages

71

• Jul 20, 2012

• #1

Hi fellows,

Situation:
I have 2 main tables (tblRecords1 and tblRecords2) with sub tables (tblHistory1 and tblHistory2). Both main tables have same structure (means same fields) and same condition with sub tables.

Requirement:
I just want to merge both main tables into one i.e. tblRecords, similarly tblHistory1 + tblHistory2 = tblHistory.

Each record in tblRecords have primary key and have one-to-one relationship with tblHistory.

Problem:
If I transfer tblRecords2 data into tblRecords1 then they must have new IDs in continuation of tblRecords1. But how could I transfer tblHistory2 data into tblHistory1. How to handle their relationship ID?

 

Kiwiman

Registered User

Local time

Today, 20:41

Joined

Apr 27, 2008

Messages

799

• Jul 20, 2012

• #2

Howzit

What I would do is

• First and foremost take a back up
• Add a new column to tblRecords1 called Record2id
• Append records from tblRecords2 to tblRecords1 but map the PK of tblRecords2 to the new field Record2Id
  • this will cause the appended records to get their new PK number in line with tblRecords1
  • and you would have a link to the tblRecords2 table in your new field Record2Id
• append tblHistory2 to tblHistory1, ensuring you link on tblRecords1 on Record2Id
• Reconcile and confirm entries are present and properly mapped
• Delete field Record2Id from tblRecords1

 

Dezirous

Rafi Ahmed

Local time

Today, 23:41

Joined

Jul 8, 2009

Messages

71

• Jul 20, 2012

• #3

Thanks for prompt response.

I didn't get the idea to change PKs of tblHistory2 records with respect to Record2Id. tblHistory2 is the only problem creating table.

 

Dezirous

Rafi Ahmed

Local time

Today, 23:41

Joined

Jul 8, 2009

Messages

71

• Jul 20, 2012

• #4

One thing which is most important and I forgot to tell is that, there is no similar data in both tables. Each record in both tables are required. :banghead:

 

Kiwiman

Registered User

Local time

Today, 20:41

Joined

Apr 27, 2008

Messages

799

• Jul 20, 2012

• #5

Howzit

Sorry been away all day. Attached is a simplifed version of what I would do.

THis has your 4 objects plus 2 queries that first populate the tblRecords1 table then the tblHistory1 table with the data from the second set of tables.

 

Attachments

• Database11.accdb

  524 KB · Views: 96

Dezirous

Rafi Ahmed

Local time

Today, 23:41

Joined

Jul 8, 2009

Messages

71

• Jul 20, 2012

• #6

Thanks man.... that's really simple and helpful. Thanks a lot....

Please, can you describe the logic behind those queries.....?

 

Kiwiman

Registered User

Local time

Today, 20:41

Joined

Apr 27, 2008

Messages

799

• Jul 20, 2012

• #7

Howzit

Qry1 simply adds the records fromtblRecords2 table to tblRecords1 - to ensure you can then populate the tblHistory1 table with the details in tblHistory2 relating to the newly added header record (tblRecords1), you map the RecID from tblRecords1 to a new field called Record2Id.

Qry2 just adds the history detail from tblHistory2 (using the Record2ID field as the link) but appending the new PK field of tblRecords1, instead of the old PK field.

I don't know what else to say - maybe study the second query to fully understand it.

Code:Copy to clipboard

INSERT INTO tblHistory1 ( RecID, Description )
SELECT tblRecords1.RecID, tblHistory2.Description
[B]FROM tblRecords1 INNER JOIN tblHistory2 ON tblRecords1.Record2ID = tblHistory2.RecID[/B];