SQL update problem

[Guthorm]

So I am importing some excel files into the access database, the problem here is the format. For example in excel I would have a question that would say

 

Who are the presidents of US - Washington

- Adams

 

the answers would be in bullet form, when I imported the file into access the two separated bullet points split up into two different rows instead of staying in one cell. So I would have a row with one line of response but without a question.

 

So this is what I am doing right now

 

Update Sheet1 Set Response = Response + (Select Response from Sheet1 where question is null and id = id + 1);

 

Sheet1 is the table name, Response is the column I want to update, id is the column with the ids in numeric orders.

 

I am trying to update the response with the response column plus response column with two criteras. It should be the row below the question I want to update, hence the id + 1, and the question column of the response should be null which is the where question is null come from. Basically any response without a question should move one up and merge with the row above it.

 

Problem is I am getting errors, so I am curious as to what did I do wrong. I am pretty newbie to SQL and I need to finish updating the damn thing by tomorrow, otherwise I will be burried by all the work I have to do.

 

So any ideas?

[Sbrideau]

You do know there's a tool you can install on Exel to do the conversion to the database? I can't recall the name, but if you want I can look for it.

[Guthorm]

I have no idea, would be helpful if you can find it for me :mrgreen:

[Markup]

Can you upload the excel spreadsheet, might be a way to fix your problem when importing instead of fixing it using SQL?

 

However,

Update Sheet1 Set Response = Response + (Select Response from Sheet1 where question is null and id = id + 1);

 

I don't know much about SQL but wouldn't you have to specify which response you are changing?

 

int ID_TO_ALTER_PLUS1;

ID_TO_ALTER_PLUS1 = SELECT id from Sheet1 WHERE question IS null LIMIT 1;

UPDATE Sheet1 Set response = (SELECT response from Sheet1 WHERE id = ID_TO_ALTER_PLUS1 - 1) + (SELECT response from Sheet1 WHERE id = ID_TO_ALTER) WHERE id = ID_TO_ALTER_PLUS1 -1;

DELETE from Sheet1 WHERE id = ID_TO_ALTER_PLUS1;

 

So,

 

UPDATE Sheet1 Set response = (SELECT response from Sheet1 WHERE id = ((SELECT id from Sheet1 WHERE question IS null LIMIT 1) -1) + (SELECT response from Sheet1 WHERE id = (SELECT id from Sheet1 WHERE question IS null LIMIT 1))) WHERE id = ((SELECT id from Sheet1 WHERE question IS null LIMIT 1) - 1);

 

DELETE from Sheet1 WHERE question IS null LIMIT 1;

 

Run these one after the other to update one record at a time

 

Obviously syntax is going to be wrong because I don't actually know SQL, but you get the idea?

[Sbrideau]

^ A loop might be useful there lol. Didn't take the time to look at your sql though.

 

Anyway, coudln't recall the name of that tool that can be installed on excel, but I've found while looking around that you can use access to open an excel spreadsheet and convert it.