At Your Survey: import data from existing Excel table?

[butbi]

Hi folks,

I have some questions about survey database with At Your Survey of
Duane Hookom:

1) How can I import the data from an Excel table to AYS database? The
Excel table looks like that:

Respondent | Question 1.1 | Question 1.2 | Question 2 | ...
User A | Answr11a | Ansr12a | Ansr 2a |...
User B | Answr11b | Ansr12b | Ansr 2b |...

If I import the whole table to Access I will get a flat database and
nothing to do with the structure of AYS but the correct data type.

If I transpose the Excel table and then copy & paste manually to
tblSrvRspns I will get the wrong data type (all are text fields)

2) How can I make a Total, Avr row in query (i.e. qxtbRspnsIDxQstnLvls)
like:

Respondent | Question 1.1 | Question 1.2 | Question 2 | ...
User A | Answr11a | Ansr12a | Ansr 2a |...
User B | Answr11b | Ansr12b | Ansr 2b |...
....
Total | TotalAnswr11 | TotalAnsr12 | TotalAnsr 2b |...

Thanks for any help!

 

[Duane Hookom]

If no one gets back to you shortly, I will try to answer later when I have
more time. Do you understand code? How many questions do you have in your
Excel file?

I would at least start by transposing the sheet and creating the records for
the questions table.

 

[butbi]

Thank you for your time and your wonderful application, Duane.
I'm newbie in Access and try to understand the code. But please be
patient with me.
My survey has about 160 questions and about 50 respondents.

As I said, I have try to transpose the Excel table to and using
copy&paste each columm (user responses) to each respondent in
tblSrvRspns. It works somehow if I only need to show the result. But if
I want to calculate the data I need to convert a lot of field from text
to number (using CDbl).

When I import the Excel shhet to Access I seen the most of fields
remain correct with their data type (text, number..). So I though there
must be an elegant way to import this data to the right tables of my
survey.

Thank you.

 

[butbi]

Oh, I forgot to tell you that I already designed the survey (created
the questions). I need only the responses.

 

[Duane Hookom]

You need to add a column "ImportColumn" to tblQuestions that corresponds
with the column name from your imported Excel table. Also make sure you have
a unique numeric ID in the imported table. This will allow you to loop
through each question in code and build an append query to append records to
tblResponses with the proper RspnsID (ID from the imported table), QstnID
from tblQuestions and the proper field from the ImportColumn field in
tblQuestion.

 

[butbi]

Thank you for your reply.
If I understand you right I have to add a column "ImportColumn" for
each user to tblQuestions. Using append query to import the answers
from each user to this table. And then I have to make an append query
to import the answers to tblResponses.
Is this right?
Can you maybe provide some code for this? Sorry for bothering you.
Thank you.

 

[Duane Hookom]

You just add a field/column to the table tblQuestions. There is no "each
user" to tblQuestions. Once you add the text field "ImportColumn" to
tblQuestions, you enter the field name from your imported Excel table into
the ImportColumn field.

 

[butbi]

May be I'm dumb but I don't get it to work. I've add the ImportColumn
and fill it with the questions like the comlun headers of Excel table.
But I don't know how to write the Append Query proberly. Can you give
me one examle of the query. My query give me the same question names
for all respondents

INSERT INTO tblResponsefromExcel ( QstnID, RspnsID, Rspns )
SELECT tblQuestions.QstnID, datafromexcel.ID, tblQuestions.ImportColumn
FROM tblQuestions, datafromexcel;

And how about the Total, Average row (my 2nd question on this thread).

Thank you, Duane!

 

[butbi]

One general question: Are all responses saved as text fields? Is there
any way to define the data type for each response. I've seen the field
RspsType inn tblQuestions but don't know how to use it. Independ what I
given all responses are saved as text.
It's a pain to convert a lot of fields from text to number to calculate
them.

 

[Duane Hookom]

You will need code to step through your tblQuestions and dynamically build a
SQL append query and run it.

All of the responses are saved as text. You could write some code or provide
some other functionality if you wanted to take advantage of the RspnsType.