Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 8, 2000

Case Study: Storing, Querying, and Analyzing Performance: Pt. 1

By Bruce Szabo

Storing Responses

How do you structure a table to store information on a questionnaire that has two types of responses options and checkboxes?

Sample Questions:

Type 1 Single Option

1. Which of the following systems 
   does your company use or plan 
   to use?
   a. Windows 2000
      i.   In Use
      ii.  Within 12 months
      iii. No plans
      iv.  Don't Know

Type 2 Multiple Options

2. Which of the e-mail systems 
   does your company use (check 
   all that apply)?
   a. Eudora
   b. AOL Mail
   c. Exchange (Microsoft)
   d. CC:Mail
   e. Outlook Express
   f. Outlook 97 or higher
   g. Groupwise
   h. Don't Know

Table Set Up:

A table was created with company information.

NAME: tblCompany
Fields:
   ID
   Name
   Address

Another Table was created with the following answer information.

NAME: tblAnswers
Fields:	
    CompanyID
    QuestionNumber
    Answer

The table setup does a good job holding the company information and answer information for option button type questions but what about checkbox answers? To solve this problem we decided to store the information as integer values representing the binary notation of the numbers.

For example, if a question has 8 possible choices we would have a matrix with the following.

Answer Value if Checked Value if not Checked
Checkbox 1 1 0
Checkbox 2 2 0
Checkbox 3 4 0
Checkbox 4 8 0
Checkbox 5 16 0
Checkbox 6 32 0
Checkbox 7 64 0
Checkbox 8 128 0

By summing the values together you can get the integer you need to store in your database. If all 8 boxes were checked your value is 255. If only box 2 and 5 are checked the value is 18.

In my follow-up article I will demonstrate how to query for specific results.



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM