Conversion Table and Update Query

[Devel84]

Hi All,

I am building an access database to manage a data conversion from multiple legacy systems (62) to a new non access database, and plan on making my Conversion Database reusable and functionally simpler.

All data is imported to one common Table structure, with the source system identified against each record.

In my legacy systems values are not coded the same ie

Title: Mr Miss Ms Mrs Record Type: Active, Inactive
System A: 1, 2, 3, 4 a , i
System B: a, b, c, d 1 , 2
System C: x, y, z, w ACT, ,INA

This applies to sooo many fields.

I want to avoid having a mapping table for each field as I will quickly have an unmanageable number of mapping tables, and complex relationships in Queries:banghead:

I would like to get to the point where I could have a single value mapping table something as follows

System, Field, Old Value, New Value
System A, title, 1 Mr
System A, title, 2 Miss
System A, title, 3 Ms
System A, title, 4 Mrs
System B, title, a Mr
System B, title, b Miss
System B, title, c Ms
System B, title, d Mrs

And some how call this in VB to go through all fields and records in a table if Old Value is found for a specific "System" and "Field" Change to new value, if noting found on Mapping table keep existing data.

What are your thoughts? Am I missing something obvious in a query, am I over complicating things?

Thanks in advance

 

[jdraw]

You will probably find that there are field definitions and redefinition/renaming in Tables, Queries,Forms and Reports.

I've worked on several systems where data management was implemented and there were names and aliases for fields in various contexts. These were by design where we had programs in Cobol, Natural, java, Oracle, .... various reporting packages. Code went through some analysers before it was approved.

However, in more and more situations (especially as presented by posters), people don't work from defined structures. They tend to invent fields and tables and structures often localized and not aware of any big picture or corporate business.

Your sample data suggests sharedLookup table(s) for things like:
Salutations/Titles
StatusCodes

and there are probably others like
Address -mailing, location, shipping, billing, HQ/corporate
AreaOfResponsibility --President, VicePresident,ChiefFinanceOfficer,ChiefTechnicalOfficer...

All this to say you may have to put some context aspect into your table.

There is no reason why a standard set of Lookup tables can not be given to programming staff/developers.

You have to work toward the authoritative tables --their design, their valuation and their availability. I think you're on a good path, but I wonder who is backing/supporting this exercise.

You may get some ideas by researching
data dictionary repository
data management
information resource management

Good luck with your project

 

[Devel84]

Thanks For your prompt reply jdraw....

My legacy systems all hold similar data but for different Geo's, all are pretty static and subject to change control protocols hence I am not finding "new" custom user defined fields, or structures.

In many cases the legacy databases are the same fundamental tool, however have been implemented by different people and teams, hence configuration values for things like Salutation, Nationality, Marital Status, address type, etc etc have been configured with different code structures, this is a fairly infinite list.

My source system is chosen, by its nature (a cloud solution) user defined fields and structures are not available. This means my target structures and configuration values are also set in stone.

Between the multiple sources and the target I have created a generic structure I can upload all legacy data too, and create extract routines to the target desired formats.

My Access database it purely acting as middleware to translate the data from legacy A/B/C etc to Target A. I don't want to build to much in to the table context as I am not sure it will add value in the long term.

I know I can create mapping tables by field in my conversion database for use in conversion queries but this will mean many (at least hundreds) of additional tables as well as relationships to maintain

If I then notice a legacy system has "country" in ISO alpha 2 and I need in ISO alpha 3, I need to create a new table, add to query/Queries and manipulate my output.

What I really need is one "Mapping Table" and one piece of VBA code which will apply all the mapping to all fields in a given range of tables.

I understand you note, there is no reason it cant be done, with my self taught approach to VBA, I am not sure where to start with the code.

Thanks For your help

 

[jdraw]

I do understand your position. I've had to bring various coding schemes together also.
Your example ISO is a good one. I remember trying to explain to some new contracted developers that a home grown table for countrycodes was a non-starter. We have a corporate table using ISO-3166, and here's how you use it. Corporate tables were managed by a separate group, and changes to 3166 (and other corp tables) were implemented and made available to other systems.

Change the corporate, authoritative tables and provide mechanism in other environments to copy a READONLY version of the table for their use. In many cases, the local systems populated their "corporate tables" at startup from the authoritative table in order to have the latest changes available.

For regions (if applicable) see http://unstats.un.org/unsd/methods/m49/m49regin.htm

We dealt with Language codes as well iso languages

You might work with some mapping table where you identify the subject area e.g Salutations, AreaOfResponsibility... whatever applies to your org, and then set up a table showing the authoritative values, and some "aliases" (those values in other systems). It could be a bit of a nightmare where codes used overlap the subject areas.

Anyway, good luck with the project. Let us know how you're doing.