Union Query Question

[jpkeller55]

I am using the attached Union Query which works fine but I need to tweak it a bit to add an additional column of data.

select 1 as temp,first([Order]) as Sequence,Question,"" as Response,"" as ResponseCriteria from table2 
group by Question
UNION select 2 as temp, [Order],"",Response,ResponseCriteria from table2
ORDER BY [Sequence], temp;

I have Table2 as shown and when the code is run, it produces Output2 as shown. I am trying to figure out how to print the QuestionCriteria in a column next to the Question as shown in the DesiredOutput.



Thanks for any help!

[ajetrumpet]

Jim,

Good to talk to you again. Glad you got your other issue fixed. As far as this is concerned, I'm not sure you can do it really. Others have more experience than I do with this I'm sure, but you're already manipulating the data abnormally in the UNION. I see you're using Allen Browne's method.

What happens when you include the criteria field both parts of the UNION? Have you tried all combinations of "" and field when adding it in? What are the results? Based on what you've already written, I would think that using "[field]" in the first part of the UNION and "" in the second part would get you close, if not what you want.

[jpkeller55]

Hey Adam,

Thanks for all your previous help...database is working pretty well.

I haven't really tried much yet...I am not anticipating too much success as you implied, but thought I would throw it out here for consideration.

I will try to work on it some this afternoon to see what might work.

Thanks much.