Multiple fields for same type of values

[RLyon]

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.

 

[RLyon]

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

 

[John W. Vinson]

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]

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>

 

[RLyon]

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?

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]

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?

I'd be inclined to have a properly normalized target table, and then just run
one (or two or three if need be) Append queries based on your UNION queries to
populate it.