Combining data into a single table, using values from alookup table

[cwillson]

Hi Peeps,

Struggling with this one....

I have two source tables, which I need to combine into a single table: Old Names & New Names.

Old Names
OLD ID	Name
12345	Martin
23456	Giles
34567	Sue
45678	Roger

New Names
NEW ID	Name	Address
9876	Martin	1 home house
8765	Giles	2 home house
7654	Peter	3 home house
6543	Michael	4 home house

The New Names table contains additional information about the individuals.

I need to combine the two using the following conditions:

1. If a name appears in both, then the single record in the target table should use the OLD ID.
2. If a name appears in only one source table, then the single record should use the source ID (either OLD or NEW)
3. If a name appears in bot, then the single record needs to be appended with the address information contained within New Names (but retain the OLD ID)

Target Table
ID	Name	Address
12345	Martin	1 home house
23456	Giles	2 home house
34567	Sue
45678	Roger
7654	Peter	3 home house
6543	Michael	4 home house

I have created a lookup table which searches for matching names (as above), and displays both the OLD ID and the NEW ID together. I have also created an Interim Table, which lists all the IDs together with one of the following labels, OLD & NEW, OLD, or NEW.

Lookup Table
OLD ID	Name	NEW ID
12345	Martin	9876
23456	Giles	8765

Interim Table
ID	SOURCE
12345	OLD & NEW
23456	OLD & NEW
34567	OLD
45678	OLD
7654	NEW
6543	NEW

I'm struggling with the query that will join populate the Name and (if present in New Name) the Address for each unique individual. I've been playing around with a SELECT IIF statement, leveraging joins, which pulls the relevant information through according to the value in [Interim Table].SOURCE, e.g. if this value is OLD & NEW or NEW then the Address field should be populated from the New Name table, but I'm not having much success.

Any help/advice would be much appreciated. I'm still very new to Access 2010 SQL, and am convinced there must be a principle, using the Lookup Table, which I'm overlooking here.

Many thanks

Chris

[ranman256]

why wouldnt you just keep the old id?
anyway, make a query to add the names to a table, and keep the old id (as long)
you can then update the table from other tables using the old id.
and still have a new id.

[June7]

Yes, can be quite challenging. Names make very poor unique identifiers. Are you positive there aren't multiple people with same name?

[cwillson]

why wouldnt you just keep the old id?
anyway, make a query to add the names to a table, and keep the old id (as long)
you can then update the table from other tables using the old id.
and still have a new id.

Always great advice, thank you ranman256!

Curiously, having not looked at this work for a few days, I arrived at the same decision this morning - to add a column to the target table which includes the new ID if the names match, then use this to enrich the old record with data from the New Name table.

The columns in the target table have been defined to support a complex data import routine, so I'll simply need to delete this "reference" column prior to import.

[cwillson]

Yes, can be quite challenging. Names make very poor unique identifiers. Are you positive there aren't multiple people with same name?

Tell me about it!! Unfortunately, the data standards and policies associated with creation and maintenance of records, in the multiple legacy systems from which the source data is coming, has been horrendous. The result is that the only key I have to go on is the name...and even this, being a free text field, requires fuzzy logic to determine matches.....

...I'll be very glad when this phase of the project is over....!! lol