Re: Extract data to new fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Rodrigo (noemail_at_hotmail.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 17:41:56 -0500

try a union query:

make this query and save it.
select SurveryId, Q1 as Question, Response from tblSurvey
union
select SurveryId, Q2 as Question, Response from tblSurvey
union
select SurveryId, Q3 as Question, Response from tblSurvey
union
select SurveryId, Q4 as Question, Response from tblSurvey
union
select SurveryId, Q5 as Question, Response from tblSurvey

then make an append query and append all records from the union query into
your new table.

Rodrigo.

Rodrigo.
"gordzilla2000" <gordzilla2000@hotmail.com> wrote in message
news:e4ea01c40b9a$2f4fbb00$a501280a@phx.gbl...
> I have a table that was created to accept the responses
> from a survey.
>
> The table was created with a field for each response to
> each question:
> SurveyID autonumber
> q1 text 50
> q2 text 50
> q3 text 50
> q4 text 50
> etc.
>
> I would like to change this to one record per question
> i.e.
>
> SurveyID autonumber
> Question_no text 2
> Response text 50
>
> A sample of records would then be:
> 1 Q1 Yes
> 1 Q2 No
> 2 Q1 No
> 2 Q2 Yes
>
> Is there an easy way to do this?
>

Relevant Pages

  • Re: Extract data to new fields
    ... select SurveryId, "Q1" as Question, Q1 AS Response from tblSurvey ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Extract data to new fields
    ... Currently the field Q1 holds the response and the Field ... >select SurveryId, Q1 as Question, Response from ... the union query into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: counting the number of responses
    ... get an error message saying "Invalid SQL statement; ... SELECT 1 as Question, as Response ... UNION ALL ... > You could try normalize your table structure with a union query. ...
    (microsoft.public.access.queries)
  • Re: Extract data to new fields
    ... INSERT INTO tmpNewSurvey (SurveryId, Question, Response) SELECT ... And the double quotes (or single quote) right after the commas on question. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: counting the number of responses
    ... The "UNION ALL" goes between SELECT statements, ... SELECT Question, Response, Countas NumOf ... the possible reponses are mostly as follows; ... The only way i can think of is an individual crosstab query for every ...
    (microsoft.public.access.queries)