Display query results using multple values in a UNION query

[jonny3000]

Hello, I am trying to query results through a union statment below. The only problem is that the query returns a error saying "The number of columns in the two selected tables or queries of a union query do
not match" Here is the code below. Can anyone help? Thank you

SELECT [A Co HBL Connecting Flights].Unit, [A Co HBL Connecting Flights].Grade, [A Co HBL Connecting Flights].LName, [A Co HBL Connecting Flights].FName, [A Co HBL Connecting Flights].[Connecting Flights].Value AS [HUB1]
FROM [A Co HBL Connecting Flights];
UNION SELECT [B Co HBL Connecting Flights].Unit, [B Co HBL Connecting Flights].Grade, [B Co HBL Connecting Flights].LName, [B Co HBL Connecting Flights].FName, [B Co HBL Connecting Flights].[Connecting Flights].Value
FROM [B Co HBL Connecting Flights];
UNION SELECT [C Co HBL Connecting Flights].Unit, [C Co HBL Connecting Flights].Grade, [C Co HBL Connecting Flights].LName, [C Co HBL Connecting Flights].FName, [C Co HBL Connecting Flights].[Connecting Flights].Value


FROM [C Co HBL Connecting Flights];
UNION SELECT [D Co HBL Connecting Flights].Unit, [D Co HBL Connecting Flights].Grade, [D Co HBL Connecting Flights].LName, [D Co HBL Connecting Flights].FName, [D Co HBL Connecting Flights].[Connecting Flights].Value
FROM [D Co HBL Connecting Flights];
UNION SELECT [E Co HBL Connecting Flights].Unit, [E Co HBL Connecting Flights].Grade, [E Co HBL Connecting Flights].LName, [E Co HBL Connecting Flights].FName, [E Co HBL Connecting Flights].[Connecting Flights].Value
FROM [E Co HBL Connecting Flights];
UNION SELECT [F Co HBL Connecting Flights].Unit, [F Co HBL Connecting Flights].Grade, [F Co HBL Connecting Flights].LName, [F Co HBL Connecting Flights].FName, [F Co HBL Connecting Flights].[Connecting Flights].Value
FROM [F Co HBL Connecting Flights];

[RayMilhon]

First take out all of the ; except the last one. Second I do have a question the [A Co HBL Connecting Flights] through [F Co HBL Connecting Flights] are these separate tables?

[jonny3000]

Yes they are all separate but they are all querys from different tables.I will update it. Thank you. Please let me know what you think.

[June7]

Don't put the semi-colon after each SELECT line, only after the last one. With that fix, looks like should work.

EDIT: Didn't see Ray's post.

I too have questions about data structure. Do you have multiple identical tables? Probably should be one table with another field for the company ID then UNION would not be needed. Resorting to UNION is an indicator data structure might not be optimized.

[jonny3000]

The tables are identical but for its purpose must be seperate tables. The purpose is to bring several companies together to analysis connecting flights. The data is actually linked to other databases and built querys of the linked tables. I took out the semi colon but the last one but I am still getting the error. When I tried to just use two tables in the union the it worked but when I added the third union then the error once more.

SELECT [A Co HBL Connecting Flights].Unit, [A Co HBL Connecting Flights].Grade, [A Co HBL Connecting Flights].LName, [A Co HBL Connecting Flights].FName, [A Co HBL Connecting Flights].[Connecting Flights].Value AS [HUB1]
FROM [A Co HBL Connecting Flights]
UNION SELECT [B Co HBL Connecting Flights].Unit, [B Co HBL Connecting Flights].Grade, [B Co HBL Connecting Flights].LName, [B Co HBL Connecting Flights].FName, [B Co HBL Connecting Flights].[Connecting Flights].Value
FROM [B Co HBL Connecting Flights]
UNION SELECT [C Co HBL Connecting Flights].Unit, [C Co HBL Connecting Flights].Grade, [C Co HBL Connecting Flights].LName, [C Co HBL Connecting Flights].FName, [C Co HBL Connecting Flights].[Connecting Flights].Value
FROM [C Co HBL Connecting Flights];

Thank you in advance

[June7]

I was able to replicate the error.

Only way I could make work was to save Access query object expanding the MVFs for each table then build UNION with those queries.

SELECT * FROM QryA
UNION ALL SELECT * FROM QryB
UNION ALL SELECT * FROM QryC;

[jonny3000]

This worked but it would't display the values for [Connecting Flights]. It just displayed #Error in each cell. Is there a way to add an alias to the field connecting flights.value or a NULL columns to match up the columns?

Thank you again

[June7]

I don't understand. I had no problem displaying data from the multi-value fields in UNION of the saved queries and did not use any alias because fieldnames were same in all tables. And the wildcard worked because same number of fields in same order.

Without being able to analyze your data directly, doubt I can be any further help. Test of tables I built with MVFs worked.

What do you mean by connecting 'flights.value'?

[jonny3000]

I finally figure it out. Just needed to base my underlying query off the [Connecting Flight].Value itself instead of adding the field [Connecting Flights] and just leave it out. Then run the UNION query as you you mentoned already and it worked. Yay!

SELECT * FROM [A Co HBL Connecting Flights Query]
UNION SELECT * FROM [B Co HBL Connecting Flights Query]
UNION SELECT * FROM [C Co HBL Connecting Flights Query];


Thank you both very much for the help