Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2004, 09:21 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Converting Flat File Database to Relational DB

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


 
Old June 5th, 2004, 11:59 AM
Authorized User
 
Join Date: May 2004
Location: , NH, USA.
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
 
Old June 5th, 2004, 01:08 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old June 5th, 2004, 01:24 PM
Authorized User
 
Join Date: May 2004
Location: , NH, USA.
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 5th, 2004, 01:50 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 12th, 2004, 09:33 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Bob and Sal,

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Flat File to Access arholly Access 16 July 9th, 2007 06:09 AM
non-relational database Michel SQL Server 2000 4 February 21st, 2006 04:05 PM
applying Filters to a relational database ggiibboo Access 3 February 2nd, 2006 02:55 AM
Post/Get from 2 Tables in Relational Database TSEROOGY Classic ASP Databases 1 November 16th, 2004 03:28 AM
Indexing XML in relational database - performance cooold XML 1 November 13th, 2003 03:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.