Prev: passthrough query question
Next: Using a Yes/No field to trigger a calculation in another field - J
From: RLyon on 23 Mar 2010 20:56
Hi, I hope this can be done.

I have a big data resource that has fields like this:

Student Name
Subject
Question1category
Question1subcategory
Question1response
Question2category
Question2subcategory
Question2response
.... up to 60 questions.

I'd like to create a query that looks simpler that I can really sort and
report on, like this:

Student Name
Subject
Question number
Questioncategory
Questionsubcategory
Questionresponse

Besides setting up 60 append queries (which wouldn't be dynamic), I'm not
sure how to do this. Is there a way in Access 2007 to do this?

Thanks in advance.
From: RLyon on 23 Mar 2010 21:16
I may have found the answer is a "union query".

"RLyon" wrote:

> Hi, I hope this can be done.
>
> I have a big data resource that has fields like this:
>
> Student Name
> Subject
> Question1category
> Question1subcategory
> Question1response
> Question2category
> Question2subcategory
> Question2response
> ... up to 60 questions.
>
> I'd like to create a query that looks simpler that I can really sort and
> report on, like this:
>
> Student Name
> Subject
> Question number
> Questioncategory
> Questionsubcategory
> Questionresponse
>
> Besides setting up 60 append queries (which wouldn't be dynamic), I'm not
> sure how to do this. Is there a way in Access 2007 to do this?
>
> Thanks in advance.
From: John W. Vinson on 23 Mar 2010 21:25
On Tue, 23 Mar 2010 17:56:01 -0700, RLyon <RLyon(a)discussions.microsoft.com>
wrote:

>Hi, I hope this can be done.
>
>I have a big data resource that has fields like this:
>
>Student Name
>Subject
>Question1category
>Question1subcategory
>Question1response
>Question2category
>Question2subcategory
>Question2response
>... up to 60 questions.
>
>I'd like to create a query that looks simpler that I can really sort and
>report on, like this:
>
>Student Name
>Subject
>Question number
>Questioncategory
>Questionsubcategory
>Questionresponse
>
>Besides setting up 60 append queries (which wouldn't be dynamic), I'm not
>sure how to do this. Is there a way in Access 2007 to do this?
>
>Thanks in advance.

You're going in a very good direction. A "NORMALIZING UNION QUERY" is the
ticket here. You need to go into the SQL window to create it; it will be
tedious but not difficult. The query would be something like

SELECT [Student Name], [Subject], 1 AS [Question Number], [Question1category]
AS Questioncategory, [Question1Subcategory] AS Subcategory,
[Question1Response] AS QuestionResponse
FROM yourtable
WHERE Question1category IS NOT NULL
UNION ALL
SELECT [Student Name], [Subject], 2, [Question2category],
[Question2subcategory], [Question2response]
FROM yourtable
WHERE Question2category IS NOT NULL
UNION ALL
<etc etc through all 60 subsets>

If you get the "Query Too Complex" error you may need to break this into two
or three subsets (questions 1-20, 21-40, 41-60 frex)
--

John W. Vinson [MVP]
From: John W. Vinson on 23 Mar 2010 21:52
On Tue, 23 Mar 2010 18:16:01 -0700, RLyon <RLyon(a)discussions.microsoft.com>
wrote:

>I may have found the answer is a "union query".

It'll work if you've paid your union dues on time. It helps if you're a
Democrat (Labour Party in some jurisdictions...) <bg>
--

John W. Vinson [MVP]
From: RLyon on 24 Mar 2010 06:14
Thanks so much for the response.

Yes it actually turns out to be quite simple if I create the first query
then go to the SQL window, because it already has the SELECT statement there.
I added a UNION ALL, then copied and pasted it again, changed the 1's to
2's. It was late and I didn't finish, so I'll finish it today.

If I do have to break it up, do I then write a second UNION query to put it
back together?

"John W. Vinson" wrote:

> On Tue, 23 Mar 2010 17:56:01 -0700, RLyon <RLyon(a)discussions.microsoft.com>
> wrote:
>
> >Hi, I hope this can be done.
> >
> >I have a big data resource that has fields like this:
> >
> >Student Name
> >Subject
> >Question1category
> >Question1subcategory
> >Question1response
> >Question2category
> >Question2subcategory
> >Question2response
> >... up to 60 questions.
> >
> >I'd like to create a query that looks simpler that I can really sort and
> >report on, like this:
> >
> >Student Name
> >Subject
> >Question number
> >Questioncategory
> >Questionsubcategory
> >Questionresponse
> >
> >Besides setting up 60 append queries (which wouldn't be dynamic), I'm not
> >sure how to do this. Is there a way in Access 2007 to do this?
> >
> >Thanks in advance.
>
> You're going in a very good direction. A "NORMALIZING UNION QUERY" is the
> ticket here. You need to go into the SQL window to create it; it will be
> tedious but not difficult. The query would be something like
>
> SELECT [Student Name], [Subject], 1 AS [Question Number], [Question1category]
> AS Questioncategory, [Question1Subcategory] AS Subcategory,
> [Question1Response] AS QuestionResponse
> FROM yourtable
> WHERE Question1category IS NOT NULL
> UNION ALL
> SELECT [Student Name], [Subject], 2, [Question2category],
> [Question2subcategory], [Question2response]
> FROM yourtable
> WHERE Question2category IS NOT NULL
> UNION ALL
> <etc etc through all 60 subsets>
>
> If you get the "Query Too Complex" error you may need to break this into two
> or three subsets (questions 1-20, 21-40, 41-60 frex)
> --
>
> John W. Vinson [MVP]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: passthrough query question
Next: Using a Yes/No field to trigger a calculation in another field - J