table design and relationships for housing survey questions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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?
 
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
 
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
 
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
 
I.e handle a varying number
of questions yet keep the number of records down?
Click to expand...

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
 
John Vinson said:
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.
Click to expand...

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.

--
 
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.
Click to expand...

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
 
Back
Top