Stumpted with Import from Excel

[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!

 

[Peter Hibbs]

Dee,

If this is a one-off operation then try the Excel to Access Converter
Utility at :-

http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

HTH

Peter Hibbs.

 

[dee]

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!

 

[ryguy7272]

Just found this yesterday:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=182

Good luck!!
Ryan---

 

[Peter Hibbs]

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.