Access questionnaires

2007/04/11 by Lassi A. Liikkanen

Using Microsoft Access to build a questionnaire

Say you want to code questionnaires filled in as prints. To store them in electronic format, you must use use some interface to a database or chart. While you can do this in Excel, SPSS, Statistica etc. it gets very difficult if the questionnaire is long. While dedicated software exist, you can also use Microsoft Access bundled in Office software package to create a questionnaire. This guide demonstrated do:s and dont:s regarding Access questionnaire implementation. Instructions apply to Microsoft Access 2002, likely applicable for other versions also.

Designing database: Tables

The format of your questionnaire and your goals on how you're eventually going to process the data will determine how you should set up your database. Most important is to look at your questionnaire and check are there same response alternatives repeated often. If they are, you should build a separate table of each option. For instance, you could have the following:

Table: Likert-6

Field NameData Type
ValueNumber or AutoNumber (counter) [primary key]
LabelText
ValueLabel
11 Strongly disagree
22 Disagree
33 Mildly disagree
44 Mildly agree
55 Agree
66 Stronly agree

Assuming you want to display a scale of six options that are internally stored using values from one to six. The reason for using numbers in the label is that they work as keyboard "accelarators", allowing a shortcut access to the desired value without problems of ambiguity that may follow if you have two labels such as "mildly agree" and "mildly disagree" that both begin with the same alphabet. It is important that you set the primary key for the Value field so everything will work more easily in the following steps.

So, as the first is to create tables for all different scales you will use in your questionnaire. After that, you can build the actual questionnaire table. With the table you will have four types of fields: unique identifier (AutoNumber), question fields linked to tables, question fields with their own predetermined set of answers and question fields for open questions.

Predetermined set of choices - database

First, you can link the table that contains the predetermined options (e.g. Likert-6) to a field. To do this, create a new field and for the Data type select Lookup Wizard.... Wizard allows you either to look up the values from a table or to type them manually. We'll select table lookup first. Next, you'll choose which table (set of options) to use. Assuming you select Likert-6 table, you must next select which fields to include. Now, you could only take the labels, but then you would only have the labels in your database. So we will use the relational database approach and select both fields (Value and Label) by clicking %gt;%gt;. The next dialogue is quite pointless (assuming you have the primary key set, otherwise you'll need to specify Value as the output format) and soon enough you're already finished!

Predetermined set of choices - custom

If you have single questions that have their clearly defined answer options, for examples Sex of the Respondent, you can setup custom options for that field. For that, start with the Lookup Wizard as you did in the previous case (database) but select I will type in the values that I want from the first wizard dialogue. Next comes the hard part and the table below shows you the idea how to do it right:

Number of columns: 2

Col1Col2
11 Male
22 Female

This means you will have two choices and both have their own index. In the following dialogue you must determine Col1 as the source of the value you want to store in the database. That's all. However, you should be aware that there are some problems with this custom value option. One clear defect is that the custom type column does not show you the label (it is the value stored in the database) but the value in the Datasheet view. Also it cannot provide value validation as table linked values automatically do. You can fix this by setting a Validation rule for the field. In the case of Sex, you could use the rule "1" Or "2" to allow only these two values. See Microsoft's KB 311172 for more information on validation.

Fields and Data types for open questions

Field data types you should not use:

  • Date/Time (inflexible)
  • Yes/No (poorly exported)

It is better to use the text type and use validating or filtering to ensure the response type. Limiting the field size is also recommended to keep the database size reasonable.

Forms

To enter data into your questionnaire table, you must create a form. My preferred strategy is to Create from using Wizard. Select your database table from the drop down box and add all fields to the selected box by clicking >>. Proceed and select Columnar layout and a style of your choice. You should end up with a quite horizontal questionnaire, which is likely undesired and thus you need to edit it in the Design view. The easiest way is to create selection regions by mouse dragging and stack up the different sections of the questionnaire. You can insert Label from the toolbox to label different groups.

The questionnaire is initially created with tab indexes that determine the order of fields, i.e. what happens after you press enter or tab in a fields. If you'd like to change the order of tabs, open the context menu by right clicking anywhere over the form design and select Tab order.... From the dialogue that opens you can determine the tab order by selecting fields and dragging up and down.

Related content:
How Windows 7 restore really works, 2014/04/26

Instructions for creating bootable Windows 7 recovery environment on a USB flash device, 2014/03/30

Disabling background software installations and upgrades in Windows, 2013/03/03

No comments for this page
Add comment | Show all comments

Name*:
Email*:
Your email will not be shown publicly or disclosed to third parties, it is used to validate messages
Location (City, Country):
* This information must be provided

Keywords: [windows] , [computers] Document's status: Ok (Document dates explained)

This document created: 2007/04/11
Modified: 2007/04/11
Published: 2007/04/11


This document's permanent URI (linking):
http://iki.fi/lassial/guides/windows/070411-Access_questionnaires

© Lassi A. Liikkanen 2007 - 2016. All rights reserved.
^Top of the Page^

*Change layout:
Printable printable
Large text

Myydään:

@lassial Twitter feed: