Union query not working

[smg]

Here is my current query:

SELECT ALIAS, YYYMM, T_DLRS FROM [FLAT_FILE_1_cost]
UNION
SELECT ALIAS, BCWS_HRS, BCWP_HRS FROM [FLAT_FILE_2_hours]
ORDER BY ALIAS;

I would expect to get the following fields in my result:
ALIAS, YYYMM, T_DLRS, BCWS_HRS, BCWP_HRS however, I only get the first three when I run it:

ALIAS, YYYMM, T_DLRS

what am I doing wrong? I am trying to join results from two queries and would ultimately like to add a couple more and add some where logic. I also tried union all and still just these three fields so I don't think it is working at all. This is my first attempt at a union query.



Thanks!

[orange]

What are you trying to do --in plain English?
The UNION query is working exactly as expected.

[smg]

When I run this union query, I only get the fields from the first query and none from the second query. What I am trying to do is get fields from both.

[rpeare]

Do each of the two queries in your union query produce results?

SELECT ALIAS, YYYMM, T_DLRS FROM [FLAT_FILE_1_cost]

SELECT ALIAS, BCWS_HRS, BCWP_HRS FROM [FLAT_FILE_2_hours]

each of these should produce results.

If they do try the statement:

SELECT * FROM (SELECT ALIAS, YYYMM, T_DLRS FROM [FLAT_FILE_1_cost]

UNION
SELECT ALIAS, BCWS_HRS as YYYMMM, BCWP_HRS AS T_DLRS FROM [FLAT_FILE_2_hours]) TestUnion
ORDER BY ALIAS;

Let me just be clear though. BOTH parts of your union query must have the proper information in the same column of each part of the union query.

For instance if your FlatFile1Cost query looks like this

TEST 201901 500.00

And your FlatFile2Hours looks like

TEST 14 12

In other words the first query is showing the date of activity and the dollar value of the activity and the second query is showing the hours it took to complete the step, your union query will not work the way you expect

I'm only stating this because your column headers appear to be different data types the first part of the query appears to be an alias, a date and a dollar value, your second query appears to be an alias and a number of hours in each of the other 2 columns.

[CJ_London]

I only get the fields from the first query and none from the second query. What I am trying to do is get fields from both.

perhaps you need to join the tables rather than a union query?

[orange]

I agree with the comments above. My concern, as Ajax points out, as that these are related tables and need a join. But the OP says "What I am trying to do is get fields from both.".

It seems (crystal ball) there is a relationship between dollars and hours, but it's a foggy crystal ball.

[smg]

let me paint a better picture. The first four fields in each query are the same (I have a total of seven queries, three shown below). I need to summarize those with the fourth and fifth fields from each query. the reason for so many different queries is due to criteria differences. Is a union query possible for this? Or, is there a better solution so I don't have to create a bunch of queries to join the data?

Query 1

ALIAS	EV % COMPL	E	YYYMM	BCWS T DLRS	BCWP T DLRS
A21.000-89	100	L	201809	5488396.55	5488396.55

Query 2

ALIAS	EV % COMPL	E	YYYMM	BCWS HRS/UTS	BCWP HRS/UTS
A21.000-89	100	L	201809	37047.23	37047.23

Query 3

ALIAS	EV % COMPL	E	YYYMM	ETC T DLRS
A21.100-89	100	L	201809	42522.48

desired result:

ALIAS	EV % COMPL	E	YYYMM	BCWS T DLRS	BCWS HRS/UTS	BCWP T DLRS	BCWP HRS/UTS	ETC T DLRS
A21.000-89	100	L	201809	5488396.55	37047.23	5488396.55	37047.23	42522.48

[Micron]

Union queries don't arrange fields side by side. You would have to UNION then Crosstab on that but it seems to be not applicable in this case.
Or put all 5 uncommon fields (that's how many I counted) in one query along with the 1st 4 common ones and put the relevant criteria for each of the 5 fields on a separate line in the query design grid, creating an OR situation for each row.

[CJ_London]

you need to join the queries, not union select

however you may still need a union query if data might be in one or two queries but not the others and you want so see these regardless

Q1
ID...Val
1.....A
2.....B

Q2
ID...Val2
1....C
3....D

Q3
ID...Val3
1.....E
2.....F
3....G

from the above you have ID 1 in all three queries but 2 and 3 do not

so you could have

ID...Val1...Val2..Val3
1....A.......C......E

and the other ID's don't show

or you could have
ID...Val1...Val2..Val3
1....A.......C......E
2....B...............F
3.............D......G

which do you want?

[orange]

An even better picture - in plain English (no database jargon)- would tell readers what EV , BCWS, BCWS HRS/UTS, BCWP HRS/UTS mean within your business. EG. For each widget (codename Alias and identified A21.000-89) we want to determine the percent completion for a specific month given the budget dollars, budget hours.....what ever, bla, bla..
Where does the raw data for your queries originate?
Good luck.

[Peter M]

Under the assumption that you need/desire a Union query because not every table is populated, you can merge these together by populating nulls in the missing fields. Once done, run a Group By query on the Union query.

[smg]

Thank you all for you help. I did create a table with all of the fields I need from separate queries with differing criteria and I am appending the results of my queries to that table. I do need help with sql in order to combine the insert into code each produces into one query. I believe that I will need to do a separate thread for that which I will post soon. Thank you!