Global Search and Replace in Microsoft Access 2

[BvCF]

Hopefully, someone can provide insight on this problem.

I have a Excel Spreadsheet with approximately 50,000 records that have "old" (previously used 2 years ago) reason codes in a column titled "Reason Code." This spreadsheet was a document that was created several years ago and since then, there are "new" reason codes that are within a local Access table. In other words, a end-user reviews a particular record and then selects a reason code using a combo box.

I need to import the excel spreadsheet into the main Access table and replace the "old" reason codes with the "new" reason codes.

My immediate response was that the Excel spreadsheet should be updated (using search and replace within Excel) with the "new" reason codes upon cross mapping the "new" and "old" reason codes in another worksheet, etc.

Is there a way to implement a "global" search and replace feature in Microsoft Access to replace the 50,000 old reason codes?

It appears that I have to do the following;

- Construct a table within Access that "crossmaps" the "old" reason codes with the "new" codes
- Import the excel worksheet into a temp table
- Add a column
- use an update query to populate the blank column with the
"new" reason code

Any additional insight as to a preferred method would be sincerely appreciated.

Thanks in advance.

 

[Darrylles]

BV,

How can you 'search and replace', when there is no link between codes?

How can the software 'know' which 'old' reason code is related with which 'new' reason code?

Do the 'reason codes' have identical 'reason descriptions'? (And I mean IDENTICAL).

If so, then you can link via description, otherwise - you have a big manual job to do.

Please explain further ...


Regards,

Darrylle







Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[BvCF]

I just received another excel worksheet that is in the following format;

New Code----Old Code---Description
ABB101------TC256------Incorrect payment
ABB203------TC229------Partial payment

Previously, I loaded the New Codes into an Access table that the end-user can select using a combo box to populate the "reason code" field in my local Access table. Note, there are approximately 70 new codes.

Now, the excel worksheet that contains approximately 50,000 records all have the "old" codes. I have to import this worksheet into my local Access table.

What would be the most efficient method to populate the
"reason code" field with the new codes for the 50,000 records that I need to import into the Access database?

 

[PHV]

A classical way:
UPDATE myTable AS T INNER JOIN myMapTable AS M ON T.Code = M.OldCode
SET T.Code = M.NewCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

PHV,

It appears that I need to load "myMapTable" to perform the update.


Thanks for the insight.