Stumpted with Import from Excel

  • Thread starter Thread starter dee
  • Start date Start date
D

dee

Hello,

I am trying to import data that is currently in Excel 2003 to Access 2003.
My worksheet contains personal data with columns including:

Province
City
Address
Tel Number
email address
FirstName
LastName
DOB
etc.

The Access tables to which I wish to import the data are as follows:

tblProvinces
ProvID (Autonumber PK)
ProvinceName

tblCities
CityId (AutonumberPK)
CityName
ProvID (FK from tblProvinces)

tblDwellings
DwellingID (Autonumber PK)
CityID (FK from TblCities)
Address
PhoneNumber
etc.

tblParticipants
PptID (AutoNumber PK)
DwellingID (FK from tblDwellings)
FirstName
LastName
Email
DOB
etc.

In Excel, there is one row per person, each containing the province, city,
address, firstname, lastname, email, dob, etc.

I'm not certain what the best way is to import the data so that it relates
as per my tables.

My first instinct is to add columns in Excel that would look up the address
and, if it's the same as the row above, put in the same identifier number.
When the address changes, start with the next number. That would at least
put an identifier for all members of the same dwelling.

Once I import the data, I could then create an update query that would
update the dwelling ID FK into the tblParticipants.

I'm reallly not certain how to import the data so that it flows properly
with Cities relating to Provinces, and dwellings relating to a particular
city and participants relating to a particular dwelling.

I hope I have explained this clearly. Any assistance would be greatly
appreciated!
 
Hi Peter,

Thanks for the reply and your program.

Yes, it's a one-off - just to get the data into Access.

Two questions:
1. Does it work with Access 2003? I see Access 2000 and XP only on your
site.
2. Will it keep household members together with one dwelling PK for
multiple people?

Thanks!
 
Dee,

Yes, it will work with Access 2003. There is actually a 2003 version
of the program on the Web site but the only difference is that it uses
Themed Controls to make the fields and buttons look nicer.

You should be able to set it up to keep the household members together
providing you have a field with a common factor that the converter can
use to group them. The documentation provided explains how to do this
in some detail.

The one thing you will need to do is to change the ProvID field in
table tblProvinces, the CityID field in table tblCities and the
DwellingID field in table tblDwellings from AutoNumber types to Long
Integer type fields with the Indexed property set to 'Yes (No
Duplicates)'. The converter program creates its own PK fields and you
cannot write a value to an AutoNumber field.

In my opinion it is NOT a good idea to use an AutoNumber field as a
link to another table anyway. This does mean, however, that when you
add new records to those three tables you will need to create the next
....ID number using VBA code but this only requires one or two lines of
code (and you are using Forms to enter the data aren't you!).

HTH

Peter Hibbs.
 
Back
Top