Re-Post: Bring 3 queries into one query

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

Ashley

I am new to Union query. Please help me with these.
What am I doing wrong?
Select FinalDate, FinalYield AS FYield FROM
qryIntranetFinalTestTotal
UNION Select PretestDate, PreTestYield AS PYield FROM
qryIntranetPreTestTotal
UNION Select SafetyDate, SafetyYield AS SYield FROM
qryIntranetSafetyTestTotal;
-----Original Message-----
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,
--
George Nicholson

Remove 'Junk' from return address.

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
Click to expand...


.
Click to expand...
 
I am new to Union query. Please help me with these.
What am I doing wrong?
Select FinalDate, FinalYield AS FYield FROM
qryIntranetFinalTestTotal
UNION Select PretestDate, PreTestYield AS PYield FROM
qryIntranetPreTestTotal
UNION Select SafetyDate, SafetyYield AS SYield FROM
qryIntranetSafetyTestTotal;
Click to expand...

The UNION query looks ok, with one exception. If you UNION three
queries each with two fields, you will get a query with two fields
(FinalDate and FYield, since it looks at the first SELECT query), with
all of the records from the three queries.

It appears that you expect to get a wider table with four fields - a
date, FYield, PYield, and SYield. You won't!

If all three queries have matching values of the date fields, you can
create a JOIN query (instead of a UNION) to join the queries "side by
side". However, if (as it appears) the FinalDate for one item is
different than the SafetyDate for that item, you won't get the right
records matching up if you join by date. Do the queries contain any
field which would let you match the items being tested? If so, JOIN
the queries on that field, rather than any sort of UNION query:

SELECT qryIntranetFinalTestTotal.FinalDate,
qryIntranetFinalTestTotal.FinalYield,
qryIntranetPreTestTotal.PretestDate,
qryIntranetPreTestTotal.PreTestYield,
qryIntranetSafetyTestTotal.SafetyDate,
qryIntranetSafetyTestTotal.SafetyYield
FROM qryIntranetFinalTestTotal INNER JOIN (qryIntranetPreTestTotal
INNER JOIN qryIntranetSafetyTestTotal ON qryIntranetPreTestTotal.ID =
qryIntranetSafetyTestTotal.ID) ON qryIntranetSafetyTestTotal.ID =
qryIntranetFinalTestTotal.ID;
 
Back
Top