Import from Excel 2003 to Access 2003

[Nate]

I would like to import an Excel file to an Access database. The Excel file
has all the data on 1 sheet, but in Access the same fields are being stored
in 3 seperate tables. Also, some patients on the Excel file are listed
multiple times and I'm not sure if the referential integrity of the one to
many relationships will be able to be enforced during the import? Thanks in
advance for any advice.

 

[Maurice]

Nate,

Let's see if i understand. You are trying to import an excelsheet which has
all data in one sheet. When imported all the data is in three tables? Do you
mean three separate fields (the columns). Referential integrity isn't checked
when importing tables. It will be checked based on the keys you set during
the setup in the wizard. If you decide to add a unique index then data will
be checked (and probably written in the table 'paste' errors). Why the three
fields? The delimeter of the data could be bugging here.

provide some more inf on the sheet you are trying to import.

 

[Nate]

I actually meant 3 seperate tables. There are about 10 fields in each table.
The Patient Info Table stores all the patients contact info - address,
phone, etc... The other 2 tables are the Visits table and the Office Table -
relating to the patients last visit and the provider they were seen by. I
have all this info stored on 1 Excel spreadsheet and I'm wondering if it will
be possible to import these into Access in a way that makes sense. Some
patients have multiple visits, so I want to make sure the 1 to many
relationships aren't lost during the import if possible. There are over
5,000 records, so it will be pretty time consuming to enter these manually -
but not impossible. I apologize for not including more details origninally -
I'm new to Access, so I wasn't sure what was relevant and what wasn't.
Thanks for your reply.

 

[Nate]

Unfortunately it's the latter. I appreciate your suggestion and I will give
that a shot, but I realize that this may not be able to happen. This will be
a learning experience so I know what not to do next time. Thanks,

 

[Peter Hibbs]

Nate,

Is this a one-off operation, i.e. you are trying to create an Access
database from existing Excel data. If so then check out this Excel to
Access Converter utility at :-

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

which should do it for you. If, on the other hand, you are importing
the data on a regular basis then that is a whole different problem and
will be quite tricky.

Peter Hibbs.