I have a flat file database designed in excel format. My database contains almost 20,0000 contact records with the following fields in one row:
companyname, address, phone, fax, email, contact1, contact2, contact3 etc. Each contact has firstname, middlename, and lastname. I would like to convert it to relational database so I would have one company with many contacts (one to many relationship). I designed a tblCompany and another tblContact. However, I see a problem populating my contact data into my tblContact.

Is there a way I can export/import my contact1, contact2 and contact3 into my tblContact? Any help is greatly be appreciated.

Thanks

You can do a normal import from Excel to Access. Then try using the Table Analyzer on the new table that is created. From the Main Menu: Tools, Analyze, Tables.

Check out the Help information on the topic "analyzer"

Hi Bob,

Thanks for your ressponse.

The Table Analyzer and similar technique allows to create one table for company and another for contact. However, I have not been able to utilize this technique to combine two contact info. in one table while I have one table for company info. As suggested I am doing research on "analyzer". Should you or any one have any different suggestion pls. let me know.

Thanks

I am thinking that with the analyzer you could make multiple passes, making three new contact tables.

After first pass:
tblCompany: Company,Address,Location,TaxID,Contact2Info,Contac t3Info
tblContact1: Company,Contact,Phone,Fax

After second pass:
tblCompany: Company,Address,Location,TaxID,Contact2Info
tblContact1: Company,Contact,Phone,Fax
tblContact2: Company,Contact,Phone,Fax

After third pass:
tblCompany: Company,Address,Location,TaxID
tblContact1: Company,Contact,Phone,Fax
tblContact2: Company,Contact,Phone,Fax
tblContact3: Company,Contact,Phone,Fax

Then you could combine the three contact tables into a new contact table, perhaps with an additional field for reference to the contact number. They would all have the same common field referencing the company.

Again, this is theoretical, untested.

What data do you have in Contact1, contact2, contact3?
define that first.

All you need to do is to import the entire file into a new table, then you can use an update or append query to put the data where it belongs into your tblContact table.

The table analyzer is limited in knowledge as far as what each field actually means to your business. It will only look for counts and of repeating values.



Sal

Hi Bob and Sal,

I think, I got it now. Thank you both for your help.