Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 5th, 2004, 10: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


Reply With Quote
  #2 (permalink)  
Old June 5th, 2004, 12:59 PM
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"
Reply With Quote
  #3 (permalink)  
Old June 5th, 2004, 02: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


Reply With Quote
  #4 (permalink)  
Old June 5th, 2004, 02: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.
Reply With Quote
  #5 (permalink)  
Old June 5th, 2004, 02: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
Reply With Quote
  #6 (permalink)  
Old June 12th, 2004, 10: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.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Flat File to Access arholly Access 16 July 9th, 2007 07: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



All times are GMT -4. The time now is 09:41 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.