Data import

[Guest_imported]

I need a hand with importing some data from an Excel spreadsheet. I am not a DBA by any means so please bear with me on this.

I have an Excel spreadsheet with the following columns.
ResponseID, Response1, Response2,..., Response99

Now, I have an Access Database with two seperate tables. The first table, Respondents, and the second table, Responses.

What I need to do is this. For each ROW of data in the Excel spreadsheet I need to import the ResponseID into table Respondents. Then, using the ID that is autogenerated from inserting the ResponseID into Respondents I need to then insert each of the ResponseX values into table Responses. Each time I insert a ResponseX I also need to store the ID of the correspondent ResponseID from table Respondents into the record as well.

Does this make any sense? If you can help I would be forever grateful.

Best.

 

[macksta]

Well well i love things like this (not).

One way to do it is using VLOOKUPS in excel very powerful.

But i am shocking at those so ill tell you another way.

1)Import all of the information into a local access table called sheet1 (You do this by File--> Get External data--> import) add an extra field ResponseID (this will be the auto generated ID)

2)Now create a Append query (i am assuming that you know how to make querys if not ask and ill explain) and insert the required data into the table Respondents.

3)Create a Update query and update the ResponseID in the table sheet1 with the ResponseID from Respondents based on the information that you put into the Respondents table.

4) Create an Append query to insert the information into the Responses table based with the ResponseID.

Hope this helps,

Jamie Mack

 

[Guest_imported]

I keep getting an error when trying to append the data to the Respondents table. The Respondents table has a number of fields that are not required. However, it does the RespondentID field which is required and an Auto index field.

Do I need to account for this when trying to append the records?

 

[Guest_imported]

Okay, I've updated the Repondents table.

Now, I have sheet1 with the following information.

ResponseID, Response1, Response2, Response3, etc. in each row.

I know that I need to make an append query now, but I'm still a bit confused with this one. Each row's column needs to be inserted into it's own record. So basically, Row1 ResponseID AND Row1 Response1 need to be inserted, then Row1 ResponseID AND Row1 Response2, and so on...for each row.

How do I accomplish this? Thanks so much for your help.

Seveni

 

[macksta]

If you are asking if you need to account for the ResponseID incrementing you shouldnt need to worry about that.

Can you Post the error that you are getting.

 

[macksta]

Ya lost me there can you explain a bit more on "Each row's column needs to be inserted into it's own record. "?

Cheers,

Jamie Mack