can parameters apply to the fields displayed? (beginner)

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

Guest

I use access fairly simply, and I can accomplish most of what I need to with
wizards. I'm stuck on a particular kind of query, and am hoping for help.

I need to run two kinds of reports: one that lists all fields for one
specified record. I've figured out how to do this using parameters in the
criteria (design view) of a simple query.
I can't get the second one to work: all responses for one specified field.
(background: I'm using Access to do a questionnaire, and fields correspond to
questions: Q1, Q2, etc. Each record is one respondent). I'd like to run a
parameter query that asks me "For which question would you like to see all
answers"? then type "Q18" or "Q2" or "Q30" etc. This then ties to a report,
and in one place I can see all responses to one question. I can't figure out
how to do this. The only thing I can figure at this point is to pre-run
queries for all questions, and have a form that specifies which query to
open. Is there any way to avoid setting up all of the queries - something
like a parameter query? Is it simply impossible?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've incorrectly set up your table. The correct table design is like
this:

Columns of table Answers:
RespondentID
Question
Response

Sample data:

RespondentID Question Response
1 1 A
1 2 D
1 3 25
.... etc. ...

Then you can run a query that gets all the answers for Question 25 like
this:

SELECT Response, Count(*) As Responses
FROM Answers
WHERE Question = 25
GROUP BY Response
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+J++4echKqOuFEgEQLq0wCdEnbRU/m/rD2oX0E5CJTo7jNLiaEAnjPO
sQDw17WKA8dDNqyGlr74qnkG
=5Wm9
-----END PGP SIGNATURE-----
 
It seems like:

PARAMETERS [Enter Question] Text ( 255 );
SELECT Switch([Enter Question]="Q1",Q1,[Enter Question]="Q27",Q2) AS answers
FROM Table1

code seems to work on my tables as they are set up now. but it does appear
to have length limitations, as in, I can only enter up to 14 fields.
 
Back
Top