Importing Data

[Dev1]

All-

I want to import excel data that is on one excel worksheet into my access database that has 5 tables in a relational format. I'm not sure how to do an import in such a way that I can import the data to the corresponding tables in access. Is there a way to import all the data into multiple talbes in one pass?

Thank you!

-Dev1

[janders468]

It will take multiple steps, I think the easiest way to do this is to either link or import the table into your database then run a series of append queries that append the portions of the spreadsheet to the tables they correspond to. You will probably run into issues of duplicating primary keys, because the spreadsheet is denormalized. You will probably want to make sure and use Select DISTINCT on all of your append queries, as well as checking for values that may have been previously entered into the database. Checking for key values that have been previously entered can be done in a number of ways. Two ways to do this are ignoring the warnings when you run the query, or by doing an outer join on your database table and filtering and only pulling in where the primary key field in your database table is null

[Dev1]

Janders468:

Thank you very much for the reply! I have found a article that illustrates just what you mentioned

-Dev1

It will take multiple steps, I think the easiest way to do this is to either link or import the table into your database then run a series of append queries that append the portions of the spreadsheet to the tables they correspond to. You will probably run into issues of duplicating primary keys, because the spreadsheet is denormalized. You will probably want to make sure and use Select DISTINCT on all of your append queries, as well as checking for values that may have been previously entered into the database. Checking for key values that have been previously entered can be done in a number of ways. Two ways to do this are ignoring the warnings when you run the query, or by doing an outer join on your database table and filtering and only pulling in where the primary key field in your database table is null

[janders468]

You're welcome, if you'd like more detailed assistance just let me know.

[Dev1]

Janders468,

I'm still having issues with this. Is there anyway to send you a private message with an attachment?

-Dev1'

You're welcome, if you'd like more detailed assistance just let me know.

[janders468]

You can send me a private message and I'll look over it and see what I can do.

[Stewart Ross]

Hi. I'm sorry but for your own protection I have removed the e-mail address posted in the previous post. As you will see in the site rules we ask all posters not to post their e-mail addresses in such an open message.

What you can do is to send a private message to the other poster in which you provide your e-mail address. Click on the name of the poster above the message and you will see an option to send a PM to the person concerned.

MODERATOR

[Dev1]

How do you send a attachment for a private message?

-Dev1

Hi. I'm sorry but for your own protection I have removed the e-mail address posted in the previous post. As you will see in the site rules we ask all posters not to post their e-mail addresses in such an open message.

What you can do is to send a private message to the other poster in which you provide your e-mail address. Click on the name of the poster above the message and you will see an option to send a PM to the person concerned.

MODERATOR

[Stewart Ross]

Hi Dev1. Sorry, but it is not possible to attach files to private messages. In your PM you would instead ask for the other poster's e-mail address, and send the attachment by e-mail after that.

It is possible to attach files to questions posted in this forum within one hour of creating a message, by clicking Edit after sending the message. This brings up a menu that includes managing attachments. There are limits to the sizes and types of files which can be attached, so the norm is to zip the contents and attach the zip file to the posted question.

-Stewart