Bring 3 queries into one query

  • Thread starter Thread starter Ashley
  • Start date Start date
A

Ashley

I have 3 queries. Each contain date and yield number.
I would like to bing three queries into one query. So that
I have Date, query1Yield, query2Yield, query3Yield.
They are not grouping by date.
Date query1Yield
1 100
2 92
3 87
Date query2Yield
1 93
2 96
3 97
Date query3Yield
1 98
2 95
3 94
The results as follow:
Date query1Yield query2Yield query3Yield
1 100 93 98
2 92 93 98
3 87 93 98
1 100 96 98
2 92 96 98
3 87 96 98
1 100 97 98
2 92 97 98
3 87 97 98
1 100
2 92
3 87
......
How can I get like below
Date query1Yield query2Yield query3Yield
1 100 93 98
2 92 96 95
3 87 97 94

Please help!
Thanks
Ashley
 
I'd suggest a UNION query to pull the data together and then a crosstab
query to display the data the way you want.
(You need to use the "SQL view" of the Query designer, not the "design view"
for UNION queries.)

"Union 1"
Select DateField, Yield, "Yield1" as Description FROM Query1
UNION Select DateField, Yield, "Yield2" as Description FROM Query2
UNION Select DateField, Yield, "Yield3" as Description FROM Query3;

Then a Crosstab query based on the Union query:
TRANSFORM Sum([Union 1].Yield) AS SumOfYield
SELECT [Union 1].DateField
FROM [Union 1]
GROUP BY [Union 1].DateField
PIVOT [Union 1].Description;

HTH,
 
Back
Top