Multiple many to many relationships (1 Viewer)

[puffer]

I need to create a database from several excel files that cause multiple many to many relationships. Each file defines a manufacturing location and a customer along with related sales dollars to the customer. The files do not follow consistent naming conventions as they come from multiple sources.

One department in the company may call plant 1 California while another calls it Calif and yet the third calls it Cal. Next one may call the customer J. Smith, another calls it John Smith and a third calls it John M. Smith.

The database needs to connect customer to plant to revenue for all of these multiple names for each then add the revenues and costs up by plant and customer and summarize them on a report. Help Please! I've thought about creating a customer and location table but am confused about how to link them when they arrive in spreadsheet form.

I think what is confusing me so is that the customers and mfg locations change every month. This company doesn't force those entering the information into multiple systems to do it consistently.

Any help or ideas would be greatly appreciated.

 

[neileg]

You can't allow inconsitencies in any data that forms a key part of your data. You have to force these names to be consitent.

For existing data you'll have to run a cleasing process that eliminates these problems. For new entries into your database, any such data sould be linked to a lookup so that the users pick the name from a list. This forces consitency without adding significantly to the effort of the user.