Union queries

[aerosmith]

Possible to union select 3 queries? All data
D-report3
D-report3-Code2-Final
d-report3-code3-f


Thanks for your time

 

[pbaldy]

Sure, should be no problem.

 

[aerosmith]

im trying this

SELECT D-Report-3-Code1-Final.Created_By, D-Report-3-Code1-Final.Rcode,
D-Report-3-Code1-Final.Amount,
FROM D-Report-3-Code1-Final
UNION SELECT D-Report-3-Code2-Final.Created_By, D-Report-3-Code2-Final.Rcode,
D-Report-3-Code2-Final.Amount,
FROM D-Report-3-Code2-Final
UNION SELECT D-Report-3-Code3-Final.Created_By, D-Report-3-Code3-Final.Rcode,
D-Report-3-Code3-Final.Amount,
FROM D-Report-3-Code3-Final;
and not getting very far

 

[MSAccessRookie]

im trying this

SELECT D-Report-3-Code1-Final.Created_By, D-Report-3-Code1-Final.Rcode,
D-Report-3-Code1-Final.Amount,
FROM D-Report-3-Code1-Final
UNION SELECT D-Report-3-Code2-Final.Created_By, D-Report-3-Code2-Final.Rcode,
D-Report-3-Code2-Final.Amount,
FROM D-Report-3-Code2-Final
UNION SELECT D-Report-3-Code3-Final.Created_By, D-Report-3-Code3-Final.Rcode,
D-Report-3-Code3-Final.Amount,
FROM D-Report-3-Code3-Final;
and not getting very far

When I reformatted your query, I spotted some errors:

[COLOR=black][FONT=Verdana]SELECT [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   D-Report-3-Code1-Final.Created_By, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   D-Report-3-Code1-Final.Rcode,[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   D-Report-3-Code1-Final.Amount[SIZE=5][COLOR=red][B],[/B][/COLOR][/SIZE][/COLOR][/FONT][COLOR=black]
[FONT=Verdana]FROM D-Report-3-Code1-Final[/FONT]
[FONT=Verdana]UNION SELECT [/FONT][/COLOR]

[COLOR=black][FONT=Verdana]   D-Report-3-Code2-Final.Created_By, [/FONT][/COLOR][LIST=1]
[*][COLOR=black][FONT=Verdana]   D-Report-3-Code2-Final.Rcode,[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   D-Report-3-Code2-Final.Amount[B][SIZE=5][COLOR=red],[/COLOR][/SIZE][/B][/COLOR][/FONT][COLOR=black]
[FONT=Verdana]FROM D-Report-3-Code2-Final[/FONT]
[FONT=Verdana]UNION SELECT [/FONT][/COLOR]
[/LIST][COLOR=black][FONT=Verdana]   D-Report-3-Code3-Final.Created_By, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   D-Report-3-Code3-Final.Rcode,[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   D-Report-3-Code3-Final.Amount[SIZE=5][COLOR=red][B],[/B][/COLOR][/SIZE][/COLOR][/FONT][COLOR=black]
[FONT=Verdana]FROM D-Report-3-Code3-Final;[/FONT][/COLOR]

The final item in your SELECT group (the one before the FROM) should not have a ",". Remove these and try again.

 

[aerosmith]

SELECT
D-Report-3-Code1-Final.Created_By,
D-Report-3-Code1-Final.Rcode,
D-Report-3-Code1-Final.Amount
FROM D-Report-3-Code1-Final
UNION SELECT
D-Report-3-Code2-Final.Created_By,
D-Report-3-Code2-Final.Rcode,
D-Report-3-Code2-Final.Amount
FROM D-Report-3-Code2-Final
UNION SELECT
D-Report-3-Code3-Final.Created_By,
D-Report-3-Code3-Final.Rcode,
D-Report-3-Code3-Final.Amount
FROM D-Report-3-Code3-Final;


Im getting syntax error in from clause

 

[pbaldy]

Due to the inadvisable symbols, you would also have to bracket the names.

 

[accessaspire219]

Hi,
I am looking to join 2 queries, with similar columns, the second query has a few columns less compared to the first query. I am using the union query to obtain a result which contains the records from both queries. The code I am using is:

SELECT
B44MinPO.material,
B44MinPO.plant,
B44MinPO.cs_qty,
B44MinPO.buyer,
B44MinPO.B44 Stock,
B44MinPO.B44 Pallet,
B44MinPO.EBELN,
B44MinPO.OPEN_QTY,
B44MinPO.WERKS,
B44MinPO.DUEDT,
FROM B44MinPO
UNION
SELECT
B44NoPO.material,
B44NOPO.plant,
B44NOPO.cs_qty,
B44NOPO.buyer,
B44NOPO.B44 Stock,
B44NOPO.B44 Pallet,
FROM B44NOPO;

However, this is giving me a syntax error - 3075 - saying -- Syntax error (missing operator) in query expression B44MinPO.B44 Stock

I have no clue why this is happening, can anyone help??
Thanks.

 

[MSAccessRookie]

Hi,
I am looking to join 2 queries, with similar columns, the second query has a few columns less compared to the first query. I am using the union query to obtain a result which contains the records from both queries. The code I am using is:

SELECT
B44MinPO.material,
B44MinPO.plant,
B44MinPO.cs_qty,
B44MinPO.buyer,
B44MinPO.B44 Stock,
B44MinPO.B44 Pallet,
B44MinPO.EBELN,
B44MinPO.OPEN_QTY,
B44MinPO.WERKS,
B44MinPO.DUEDT,
FROM B44MinPO
UNION
SELECT
B44NoPO.material,
B44NOPO.plant,
B44NOPO.cs_qty,
B44NOPO.buyer,
B44NOPO.B44 Stock,
B44NOPO.B44 Pallet,
"",
"",
"",
"",
FROM B44NOPO;

However, this is giving me a syntax error - 3075 - saying -- Syntax error (missing operator) in query expression B44MinPO.B44 Stock

I have no clue why this is happening, can anyone help??
Thanks.

Posting a new problem on a new thread can help you to get assistance faster, and makes it easier to follow issues for the people who are readinjg along to learn.





As for your issue, I see at least two issues to address:

1. The Union Query should not work, and should return a message indicating that the two queries do not have the same number or type of columns. UNION Queries require that the combined queries not only have the same number of columns, but the same type of column in each position. To remediate this, you can add placeholder columns for any that are missing, in order to align the columns properly.
2. You have Commas "," following the last entry (before the FROM). The last entry does not need the ",". Remove these.

FOLLOW-UP:

pbaldy makes two additional good points

1. I missed the fact that some of your column names contained spaces. If then need to stay that way, surrounding them with brackets ("[" and "]") will be necessary as well.
2. I assumed that all of your fields were strings. If they are numbers, then you need a default value such as 0

 

[pbaldy]

Spaces are also inadvisable, and also require the use of brackets. The next error you'll run into is that a UNION query requires the same number of fields in each SELECT clause. You can use placeholders for the needed fields in the second SELECT, such as:

...0 AS EBELN, ...

 

[LPurvis]

And unless you are specifically looking to eliminate duplicates - I'd always advise using UNION ALL by default - it can easily make a noticable difference.

And aliasing is a wonderful thing. Avail yourself of it.
Nasty looking (and awkward to read) queries suddenly take on a clarity otherwise obfuscated.

Cheers.

 

[accessaspire219]

IT WORKED!! Thank you SO MUCH!! I appreciate your help!!