Hi all,

I'm trying to do a data migration from mySQL to SQL Server. There seems to be major data 'massaging' that needs to be done, and so I'm using Access as the intermediary.

I've imported the table(s) into Access, and now I want to transfer it into another database (again, in Access), but with a different structure.

For eg: the original database may have a main table with fields such as caseid, casedesc, caseuser, casepriority & casesolution. All these fields except the id, are text fields and have full text in them. ie a row would be like below

113 SystemCrash JoeSomebody Urgent Fixed using Linux

However, the database in which I intend to transfer this to has a main table with similar fields, however, these fields are references to other data tables. Ie a row would be

120 SystemCrash 12 4 Fixed using Linux

Here, CaseUser refers to Record#12 in tblUsers while Case Priority refers to record#4 in tblPriority.


Of course, there are many more fields in the one main table, most of which reference other tables.

Here is where I need the suggestion from all you experts. How do I go about doing this? I'm having a problem carrying over the referenced cases, basically at a loss on how to go about looking up the ID for the text in question, and then inserting that into the final db.

All help Much Appreciated.

Thanks,

Deadlee