import related records

[Deb H]

I will be importing records into an Access database. Each record will contain
a company name and contact person along with other fields. Because each
company has many contacts, I am setting up a main company table and a related
contacts table. However, the text file that I will import has all the records
in one table. My question is, how can I separate this data into the two
tables? Do I import it all into one table and then split it afterwards in
Access (and if so, how?) or does Access have a tool for separating the data
during the import? I'm not sure how to go about this and keep the
relationships intact. Thanks in advance for any advice you can give me. I am
using Access 2003.

 

[Jeanette Cunningham]

Hi Deb,
It may be easier if you put the table data from Word into an Excel
spreadsheet.
You can set the spreadsheet up the way you want the data for your access
database and import the spreadsheet. You could put the data for each table
in a separate worksheet if you wish.

Jeanette Cunningham

 

[Deb H]

A follow up question - I'll have thousands of records that need to be
imported on a monthly basis. I'm trying to understand how I can split these
into my main and related tables without manually doing the split and setting
the Company ID field for each related contact. I'm probably missing
something, but how could I use Excel to make this process efficient? or would
the Access table analyzer work for me? I tried the analyzer on some sample
data, but I'm not sure it's the best solution.
Thanks for your help.

 

[Jeanette Cunningham]

Deb,
You can import the data into a new or temporary table that we can call a
staging table.
The next steps are to use queries to extract only the data you want and add
it to whichever table you need to.
You could even do it by just linking to the data without importing the whole
word table first.
Just extract the data you need for each query.
I have seen a company directory distributed as a text file and used as the
data source for an access database.

Jeanette Cunningham

 

[John Nurick]

Hi Deb,

The general idea is to link to the text file and use append queries to
extract the relevant information from it into your two tables.

1) The first query needs to identify companies in the text file that
do not exist in the companies table and append the relevant records.

The complication - usually - is that there's no key that a query can
use to decide with certainty whether a company that appears in the
text file is the same as one that's already in the database. If you're
lucky, there'll be a company registration number or some such that you
can rely on to be unique; otherwise you'll almost certain to be faced
with deciding whether
Acme Steel Corporation
and
Acme Steel, Inc.
at the same address are or are not the same organisation.

2) Once the companies are imported, you need to use a query that links
the Companies table and the text file (so that the primary key of the
Companies table is available) and appends the contact data to the
Contacts table.

3) You also need to decide what to do if a contact in the text file is
already in the database. Do you update your existing contact record,
or assume that it's another person of the same name, or what? Same for
company records.