Multiple many to many relationships

[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.

 

[FoFa]

If you can not force data input integrety, you have a big problem. First have you considered sending each of them a standard entry form in Access or even Excel to consolidate entry (with drop downs to keep naming consistant)? If this is "just the way it is" than I could suggest this.
Link each spreadsheet, use a query to take that data and put it in a local table. Then, write a bunch of queries that take the info from that table and convert the data to a known, and move the data to internal tables, I would use some flags on this input table to flag the ones you processed (converted) so you can query those flags and see what is left to do. typically you can get this process down to a bunch of queries running from a macro, and man handling a few exceptions.
But your time would be better spent forcing integrity at input.

 

[puffer]

Thank you for the macro idea of running the queries. I've written multiple queries and was hoping maybe there was an easier way but you are right I'm completely out of luck integrity just isn't going to happen !!!! How does the flag work? I've written things in VB with flags but never in Access?

 

[FoFa]

Basically what you are doing is taking data from Excel, putting it into an internal table. Running queries to "clean" the data. On this internal table, create a flag (boolean) or even multi-flags. Then there are many to do this depending on your data, but you basically set this/these flag/s depending on the work the queries do. What I like to do is take the raw data, run my clean queries. Than run a bunch of check queries (most likely many, usually checking data for NULL or against another table for validation, etc). Any that are in error I set this flag, or all that are correct I set this flag, one way or the other, just do it the same for all. Than a final list all rows that have this flag set/unset, and I know what is left to work. I usually have 2 macros, one for fixing and one for checking, that way I find it is easier to add more queries too later (which also seems like a never ending battle).