Union?

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I have two queries i need to bring together. Do I need a union and how
would i go about it?

Query 1
ID
CostSource
1999
2000

Query 2
ID
EstimateSource
1999
2000

Where CostSource and EstimateSource are the same type of field. Both ID
fields have the same type as well. I need to bring together both sources
into one field with their corresponding data.

Example:

Query1
ID |CostSource |Cost1999 |Cost2000
222 |Cost-Share |$256
222 |Maintenance |$987 |$980
255 |Cost-Share |$523


Query2
ID |EstimateSource |Est1999 |Est2000
222 |Maintenance |$890
255 |Cost-Share |$256
255 |Maintenance |$564 |$122


Output
ID |Sources |Cost1999 |Est1999 |Cost2000 |Est2000
222 |Cost-Share |$256
222 |Maintenance |$987 |$890 |$980
255 |Cost-Share |$523 |$256
255 |Maintenance| | $264 | | $122


I Can't figure out how to group the sources together with the corresponding
data.

This query is going to end up as a report.
 
Hi,


In theory, it is a standard join, a full outer join, to be safe.


SELECT DISTINCT ID, CostSource As Source FROM Query1
UNION
SEELCT ID, EstimateSource FROM Query2


Save it as Qu1. That lists all the possible (ID-Source) involved.


Then, the real query is


SELECT Qu1.ID, Qu1.Source, Cost1999, Cost200, Est1999, Est2000
FROM ( Qu1 LEFT JOIN Query1
ON (Qu1.ID=Query1.ID) AND (Qu1.Source=Query1.CostSource ))
LEFT JOIN Query2
ON (Qu1.ID=Query2.ID) AND
(Qu1.Source=Query2.EstimateSource ))




Hoping it may help,
Vanderghast, Access MVP
 
I am getting a error message when running the Qu1 query you suggested.

"Syntax error in FROM clause"

Here is what i have. I have changed some of the Names and fields to what I
actually have in the database. I simplified it the first time.


SELECT DISTINCT ID,
SourceCost As Source
FROM subqryEstCostReport-Cost3
UNION
SELECT ID,
CostEstPaySource
FROM subqryEstCostReport-Est2;
 
Thanks Michel,

After a little fiddling I got it to work. Many Thanks. I 've been trying
to get it done for days now. I love this Newsgroup!

Andrew
 
Hi,


Indeed, I typed SEELCT, in the first query, should be SELECT, and I
have an extra closing parenthesis, in the second query (the very last is the
extra one to be removed) due to excessive cut and paste.



Vanderghast, Access MVP
 
Back
Top