Import from Excel 2003 to Access 2003

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.

Hi -

There are probaly other ways to do this, but here's what I would do:

Instead of importing the Excel file, create a link to it. Then, create three
append queries to add records to your three tables. Load the "one" side of
the one-to-many relationships first, to avoid referential integrity issues.
For the case with duplicated data, set the "Unique Values" property of the
query to "Yes".

One question, though - does the Excel Spreadsheet contain the required PK
values for the three tables, or are you using autonumber PK's? If the latter,
the exercise becomes more complicated.

HTH

John


Nate wrote:
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.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

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.
--
Maurice Ausum


"Nate" wrote:

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.

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.

"Maurice" wrote:

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.
--
Maurice Ausum


"Nate" wrote:

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.

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,

"J_Goddard via AccessMonster.com" wrote:

Hi -

There are probaly other ways to do this, but here's what I would do:

Instead of importing the Excel file, create a link to it. Then, create three
append queries to add records to your three tables. Load the "one" side of
the one-to-many relationships first, to avoid referential integrity issues.
For the case with duplicated data, set the "Unique Values" property of the
query to "Yes".

One question, though - does the Excel Spreadsheet contain the required PK
values for the three tables, or are you using autonumber PK's? If the latter,
the exercise becomes more complicated.

HTH

John


Nate wrote:
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.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

.

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/f...9d za3z4c9fd6

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.

On Mon, 22 Mar 2010 13:05:01 -0700, Nate
wrote:

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.

"Maurice" wrote:

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.
--
Maurice Ausum


"Nate" wrote:

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.

Hi Nate -

All is not necessarily lost. You should be able to at least get the patients
table loaded properly, with an autonumber PK. Now, as long as you can
uniquely identify each patient using fields OTHER THAN the autonumber, here's
what you can do:

After you have added the patient data to a table, set up a select query
(which will eventually be an append query to the visits table) containing the
patient table and the linked spreadsheet. Join these two entities using all
the fields (and only those fields) required to uniquely identify each patient.
Set up the query to display the fields that will be included in the Visits
table, PLUS the PK from the patients table (I'll call it the Patient_ID).
This Patient_ID will be included in the Visits table as as foreign key (FK).

Run the query (it should still be a Select query). The number of rows should
match the number of rows in the original Excel spreadsheet.

If the results are as expected, you can convert the select query to an append
query to append the data to the Visits table. This table must contain the
Patient_ID as a FK as I mentioned, and it can have its own autonumber PK,
generated as you append the data.

I'm not sure about the Office table - can you clarify what the data is in
the Excel spreadsheet regarding Office?

HTH

John


Nate wrote:
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,

Hi -

[quoted text clipped - 20 lines]
many relationships will be able to be enforced during the import? Thanks in
advance for any advice.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1