Tables- Multiple identifiers

[jeff]

I have 4 tables with many companies data from 4 different sources. Each data
source identifies a company differently. How can I best relate these tables?
I need to be able to lookup a company with a single identifier that will
then show me the data from each data source. I don't want to have to learn 4
different identifiers to look info up in separate tables.

Thanks for the help...

-jeff

 

[KARL DEWEY]

It ain't easy --
I suggest building a fifth table with a primary key. Populate the table
from one of your tables that you like the best. Add a foreign key field to
each of the orignal tables. Insert the primary key in the foreign key field
for matching record. Set a one-to-many relation between the new table and
the others. Edit the data in the new table to common information - like IBM
instead of International Business Machines Inc.

 

[Rev]

Multiple tables with different IDs are a pain in the brain! Are any of the
same companies overlapping in more than one table? Or are the records in
each table unique against the others (this table holds all NY companies, that
table all CA....
There really is no way of getting around the Unique Identifier issue, you
simply can't make a match if there isn't a match. Like SSN or here's a good
one, CompanyID. At some point, you will need a relational database system
capable of matching one to many relationships between the tables, no
information should be repeated in different tables exept IDs from other
tables that allow for them to be matched up with each other..

Why the four tables? Is there a compelling business reason for keeping the
data seperated unto islands to themselves? If there is no good business
reason for them being seperated, they should be consolidated.
Regardless, if the data has basically the same structure you can use a Union
Query so that they all look as if they are in one table. this works if you
have enough fields with the same data types and information category (Name,
Address, Phone Number, Age, Sex... etc... you can run a Union operation that
virtually makes your table into one table. At least then when you sort the
data, you will probably find duplicate values together in alphabetical order.
See 'Union Query' in Access help or Examples of Union Queries in Access Help.