Union Query

[nathan_savidge]

Hi

I have a union query that basically, puts all the results from 16 queries
into 1, as desired. However, due to the names of the fields, i have had to
structure my query as:

Select result_query1,0,0,0
UNION
Select 0,result_query2,0,0
UNION
Select 0,0,Result_query3,0


This works, but i would like this to read as Result 1,Result2,result3

The way that i have done it, i get the first result, then 0's, then 0's then
2nd result followed by 0's and so on.

Is there a way to have it such as:
Result 1 Result 2 Result 3
Name

With no 0's

Thanks

 

[Danny J. Lesandrini]

I think you're just talking about aliasing, and I believe you only need to
alias the first query, so it would look like this ...

 

[John Spencer]

Perhaps you could use something like the following. I've used UNION ALL
instead of UNION as that should speed up the query since Access (Jet)
won't need to reduce the record set to unique records.

SELECT [Name] as TheName, "Result01" as SourceField, Result_Query1 as Result
FROM QueryOne

UNION ALL

SELECT [Name], "Result02", Result_Query2 as Result
FROM QueryTwo

UNION ALL

SELECT [Name], "Result03", Result_Query3 as Result
FROM QueryThree

Now USE that query in a crosstab query.

TRANSFORM First(SourceField) as sValue
SELECT TheName
FROM qUnionQuery
GROUP BY TheName
PIVOT SourceField

By the way, it is usually best to post the actual SQL of the query. I
realize that your union query may have been rather large, but you could
have posted the first 3 (or so) sections of the union query. What you
posted would error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================