Option groups, radio buttons and reports...oh my!!

[La Diva]

Hello all!
I am up the creek and need your assistance asap! I have created an access
database with a survey form that contains 15 option groups each with 4-10
radio buttons. I need to be able to print reports based on what is returned.

My issues are:
a) for the smaller option groups, instead of printing 1, 2, etc. for my
returned value, I need it to state for instance the actual city name on the
report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the
report according to that city name (all records with Las Vegas will have Las
Vegas as a subtitle and then the rest of the associated data for each
associated record underneath).

b) I still need the larger option groups to retain their numbers of course
as they are ratings (1-10), but how can I count how many records have rated
the service a 7?

I really appreciate your help on this.
Thanks in advance,

La Diva

 

[KARL DEWEY]

In the query for the report join the table that has the city on the number.
Then use the city name instead of number.

 

[La Diva]

Thanks for your reply Karl!
Does this mean I would have to have a separate table containing 2 fields
(city and associated number) for each option group?

 

[KARL DEWEY]

That is how I would do it.
Maybe others know different way.

 

[La Diva]

or do you mean make a conditional statement? If so, what is the correct
format? Does it go in the criteria line?
i.e. criteria: 1="Montana", 2="San Francisco", 3="Jersey City"

Thanks for your patience...8o|

 

[KARL DEWEY]

If only have a couple of cities you can do it with nested IIF statement in a
calculated field but a table would be a whole lot easier to change/add.
City: IIF([CityField] = 1, "Las Vegas", IIF([CityField] = 2, "New
Jersey", IIF([CityField] = 3, "Next city", "Unknown")))

 

[Ron2006]

b) I still need the larger option groups to retain their numbers of
course
as they are ratings (1-10), but how can I count how many records have
rated
the service a 7?


The answer to this one depends quite a bit on how you are going to
present the information on a report, etc. and how many groups like
this that you have.

One method is:

In a SUM/Group BY type of query, define 10 fields, each with the
action of SUM, and more or less in the following pattern:


QuestionACntAnsw1: iif([tblname]![OptionGroup1]=1,1,0)
QuestionACntAnsw2: iif([tblname]![OptionGroup1]=2,1,0)
QuestionACntAnsw3: iif([tblname]![OptionGroup1]=3,1,0)
QuestionACntAnsw4: iif([tblname]![OptionGroup1]=4,1,0)
etc

It is brute force but will work.

If this is for a report via the "Report" area there are other ways of
doing the same thing I believe within that took specifically.

 

[ken]

I'm afraid that your problems are rather more fundamental than appear
at first sight. It sounds like you've made the common mistake when
designing a 'questionnaire' database of using separate columns for the
questions. Its what's known as 'encoding data as column headings'. A
fundamental principle of the database relational model, 'the
information principle' is that data is stored as explicit values at
column positions in rows in tables, and in no other way. In a
relational database one approach would be to have a table of
Questions, a table of Answers and a table QuestionAnswers which models
the relationship between them by having two foreign key columns, each
referencing the primary key of the Questions and Answers table. In
combination these two columns constitute the composite primary key of
the table.

Consequently each question will be represented by one row in
Questions, each answer by one row in Answers and each possible
combination of a question and answer by one row in QuestionAnswers.

For each respondent's set of answers you need another table, Responses
say, with ResondentID, QuestionID and AnswerID columns, the first
being a foreign key referencing the primary key of a respondents
table, the latter two a composite foreign key referencing the
composite primary key of QuestionAnswers. If each respondent can give
only one answer to each question then the primary key of this table is
a composite one of RespondentID and QuestionID; if a respondent can
make multiple answers per question then the primary key is a composite
one of all three columns.

You might also want to introduce a QuestionCategories table so that
you can categorize each question by means of a foreign key column
referencing the primary key of Categories. This would help you sort
the data for reporting purposes.

With a model such as the above querying the database becomes very much
simpler as it is simply a matter of joining the tables as necessary
and aggregating the relevant data, e.g. to count the number of
instances of each answer to each question would require a query
grouped by question then answer and counting the rows per answer with
COUNT(*).

For data input the simplest way would be a subform based on Responses
within a single view form based on Respondents. The subform would
have combo boxes for question and answer so its just a case of
inserting a row for each question answered by selecting from the combo
boxes. This is fine if the data is being input by an operator on the
basis of paper questionnaire forms completed by the respondent, but
less suitable if the respondent is inputting the data directly into
the database via an Access form. For this a better solution is to use
an unbound form, which can use option groups as you've done, and to
read/write the data to the table in code behind the form. This is not
difficult but does need some knowledge of writing VBA code using
either DAO or ADO.

If you don't want to design your own from scratch you can download an
application (Duane Hookom's 'At Your Survey') which demonstrates how
to design your own survey database from:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

It includes sample surveys and a brief manual.

Ken Sheridan
Stafford, England