Build an Access database to display survey results

May 14, 2011 5:50 PM

I've never used Access; I'm a SQL snowflake and don't take naturally to its norms. Pretend I'm your technology-challenged elderly relative, and tell me how to set up tables to run queries on survey results.

I created a survey in surveymonkey, with a combination of free text questions, multiple choice/single answer, and multiple choice/multiple answer questions. Also built in page and question logic. I know it's possible to set up a relational database (and surveymonkey generates relational database tables), but I can't quite picture how I should modify the spreadsheets.
An example of user A's results:
Page 1 - (free-text) name; (multiple choice/single answer) continent
question logic jump
Page 4 (Europe) - (multiple choice/single answer) country
question logic jump
Page 10 (France) - (multiple choice/single answer) Preference 1
question logic jump
Page 35 (free text Preference 1 questions) (multiple choice/single answer) Preference 2 or Done
question logic jump
Page 50 (free text questions) Done

Users B - ZZ will use the same general framework, but land on different pages based on responses to questions with logic. The Continent pages are all alike, as are the country pages and preference pages -- they're asking the same questions about the user's choices in the survey.

I want to be able to query on everything and in combination (e.g., all users in North America; all Preference 1 users regardless of geography; all Africa users who selected Preferences 3 and 5). Help me with the table logic, pretty please (N.B., I do not need assistance in actually creating the tables, just the foundation logic for doing what I want to do).

posted by heigh-hothederryo to computers & internet (4 answers total)

Hm, I think I should have posted the above to accessmonster. But if anyone has any ideas, I'll take them.
posted by heigh-hothederryo at 6:24 PM on May 14, 2011

If you want to learn SQL, this is a great tutorial.

This tutorial is pretty agnostic about the implementation, whether its Access or MySQL or any one of many hundreds of SQL database engines out there. The goal is simply to teach you SQL.
posted by Blazecock Pileon at 7:58 PM on May 14, 2011 [3 favorites]

So, the most basic way to set it up is with every user is as row, and every question is a column. This doesn't use relations, and isn't super-efficient. But you don't have a very complex data model, so it works. FWIW, you could also do this with filters in Excel.

In that case your queries look like
select * from t_survey_results where f_continent = "North America" -- or "1" if that's indexed...
select * from t_survey_results where f_preference_1 = true;
select * from t_survey_results where f_continent = "Africa" and f_preference_3 = true and f_preference_5 = true

Now you can get more clever, since the preferences are all the same. You'd create two tables, t_user and t_user_preference and you'd use joins in your query.

t_user would have 4 columns (or more), f_user_id, f_name, f_continent, f_country
t_user_preference would have a key back to t_user (f_user_id) and a bunch of boolean preference flags. Note this only works if the prefs really are the same between all the pages.

Your query would look like this (depending on the language of the DB you used, but the idea of a join is the heart of the 'relational' part of the relational database...)

select TU.f_name from t_user TU
join t_user_preference TUP on TUP.f_user_id = TU.f_user_id
where TU.f_continent = "Africa"
and TUP.f_preference_3 = true
and TUP.f_preference_5 = true;

Again, this may be overkill, but that's the basics of it.
posted by Mad_Carew at 8:35 PM on May 14, 2011

After you have mastered basic joins (and that might take a while) get to understand this graphic. I did not read the article, I simply wanted to include the image but the article looks like it has good stuff.

Note: Not all SQL's include all of these joins, but it would be a good idea to get the patterns in your head to start thinking in 'sets'.

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx
posted by santogold at 10:21 PM on May 20, 2011