Hi all,

another newbie question I'm afraid.

I had a look at the databases our lab has set up, and unfortunately they are basically glorified spreadsheets - so for example it looks like this

product A ordered by

ABC Maria

CDF Ron

AGG Maria Nova

where Maria and Ron are simply entered text. So I see two problems:

• sometimes the same person is entered under different names (for example with or without family name) or simply misspelled
  
  
• there should be a table "people" and each name should just be a link to the table entry
  

So my questions are:

- is there a way to clean up the database?

- is there a way to batch replace names with table entries

I should note that there are several such databases, each with 2,000 to 5,000 entries, so I would not want to do this by hand.

Thanks

Markus

P.S. In my defense: I'm currently working my way through some tutorials (boring as heck - they seem tailored to someone who sees a computer for the first time) and nothing has come up yet to help me with my problem.

- is there a way to clean up the database?

Yes - if all entries are non-ambiguous (e.g. there is only one person named Maria).

- is there a way to batch replace names with table entries

Yes, after some manual work, such as associating both "Maria" and "Maria Nova" with the same record in the People table (assuming they are one person - I am not sure what the "ABC" and AGG" stand for).

each name should just be a link to the table entry

Actually, it would be best to use a PersonID as the link. Names change, get misspelled, etc.

Yes - if all entries are non-ambiguous (e.g. there is only one person named Maria).

That much at least should be the case.

Yes, after some manual work, such as associating both "Maria" and "Maria Nova" with the same record in the People table (assuming they are one person - I am not sure what the "ABC" and AGG" stand for).

They stand for some item (could be cars, oligos, chairs, etc)

But how do I associate?

Actually, it would be best to use a PersonID as the link. Names change, get misspelled, etc.

That's what I have in mind too, but I'm stuck at the associating ;-)

But how do I associate?

I would start by finding all records, sorting by name and exporting grouped by name. This will give you a list of all names used in the table.

The next step is largely manual. The goal is to turn:

Maria

Maria Nova

M. Nova

Ron

...

into:

PersonID   FirstName    LastName    Names

1          Maria        Nova        Maria¶Maria Nova¶M. Nova

2          Ron          Smith       Ron

...

where Names is a return-separated list of all names used for a single person.

In step 3, you create a relationship between the two tables, matching People::Names to YourTable::Name. Use this relationship to populate the (newly added) PersonID field in your table with the PersonID value of the related record in People.

Finally, switch the relationship to match on PersonID.