table design and relationships for housing survey questions

[Guest]

I am trying to construct a database to house survey questions that are
imported via excel.

The data from excel comes with the following column headings

SurveyID Location Date Q1 Q2 ....... to Q60

My question is how to link these question answers to the actual survey
questions in another table.

My problem is that I need the structure of this table to have the following
column headings:

Question# Questiontext

Because these questions get grouped by larger measures.. ie. the first 5
questions are for one goal.

How can I link these tables without having an individual table for each
question?

Any help would be greatly apreciated.

Darren

 

[tina]

the Excel data Q1 to Q60, is that the *questions*? or the *answers* that
respondents gave to the questions? assuming it's the answers, suggest you
try something like the following:

two tables

tblSurveys
SurveyID
Location
Date

tblSurveyResponses
ResponseID (primary key)
SurveyID
QuestionID
Response

if i understood you correctly, you already have the survey questions in
another table that has some sort of "QuestionID" primary key field. i would
import the Excel data into a "temporary" table, then use Append queries to
link each answer column to the correct question in your questions table -
one column at a time - and dump the data into tblSurveyResponses. once
you've appended all the answers to the permanent table, you can link
questions and answers as needed on the "QuestionID" field.

hth

 

[Guest]

Thanks Tina,

I have set up my tables similar to what you suggested. But the trouble I am
having is how to tie the question# and the actual question in tblquestions
(my table of questions) together when I am doing the append query?

 

[tina]

cleaning up "dumped" data, always a load of fun.
assuming that this is a "one time" dump, i would just do it manually rather
than creating an automated process. create your append query to append a
single column of responses from the temporary table into tblSurveyResponses.
in the query design view grid, manually enter the correct QuestionID (from
tblSurveyQuestions) that you want to append into field QuestionID in
tblSurveyResponses.

after running the append query, change the design to append the next column
of responses, and change the QuestionID to match, and run the query. repeat
until you've dumped all the columns of data from the temporary table into
tblSurveyResponses.

hth

 

[Guest]

Thanks Tina,

Yes, that would be the easy way out, but you guessed it... I have to make an
automated process to allow for future years use and a varying number of
questions (ie. some surveys have 30 questions some have 40). This makes it
way more fun.

It seems like I will have to use some sort of dynamic sql statement to allow
for the varying number of questions unless I can figure out a way matching
the column heading during import (in the excel file) to the questionID and
append the columns accordingly. But there has to be an easier way!

Darren

 

[Duane Hookom]

If you have lots of these to do, you might want to look at how this is done
in At Your Survey found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Hi Duane,

Actually I have set up my tables similar to what you did in your sample
database. The only trouble I am having is the tblresponses gets large fast if
you have a big sample (like mine is close to 100,000 with a varying number of
questions 30 to 40 depending on survey type) is there a way of housing the
responses without making the file size balloon? I.e handle a varying number
of questions yet keep the number of records down?

Regards,

Darren

 

[John Vinson]

I.e handle a varying number
of questions yet keep the number of records down?

100000 records is very modest. Access can handle millions.

And storing one small record per response causes much LESS bloating
than the overhead of storing wide-flat records with many unused
fields. Sure, Access doesn't store nulls - but each field still has
its overhead.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

100000 records is very modest. Access can handle millions.

storing one small record per response causes much LESS bloating
than the overhead of storing wide-flat records with many unused
fields. Sure, Access doesn't store nulls - but each field still has
its overhead.

I assume by 'Access' you mean Jet. Speaking of overhead, have you ever
tried creating a JOIN between two tables of one million rows each?
Sometimes denormalized tables can be justified on performance grounds.

Jamie.

--

 

[John Vinson]

I assume by 'Access' you mean Jet. Speaking of overhead, have you ever
tried creating a JOIN between two tables of one million rows each?
Sometimes denormalized tables can be justified on performance grounds.

Yes, I've done so; and yes, I denormalized. It was in Oracle and it
was (at the time) up to 3.5 million rows.

My resume includes a line "Judicious denormalization, only when
necessary"... <g>

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps