Help with inserting a 'UNION ALL' *SQL query* (1 Viewer)

[3dman]

Hi all,

I need assistance in putting 'UNION ALL' into the following SQL statement:

SELECT [zTBL_STATE_GROUP_TABLE 3].STATE, [zqry_nsw/act inconjunctiontable 3].[Total (in conjunction)], [zqry_inconjunction_QLD TABLE 3].[Total (in conjunction)], [zqry_inconjunction_SA/NT TABLE 3].[Total (in conjunction)]
FROM [zqry_nsw/act inconjunctiontable 3] RIGHT JOIN ([zqry_inconjunction_SA/NT TABLE 3] RIGHT JOIN ([zqry_inconjunction_QLD TABLE 3] RIGHT JOIN [zTBL_STATE_GROUP_TABLE 3] ON [zqry_inconjunction_QLD TABLE 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE) ON [zqry_inconjunction_SA/NT TABLE 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE) ON [zqry_nsw/act inconjunctiontable 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE;

I know that the 'UNION ALL' should be placed after the 'FROM', but here it has 'RIGHT JOIN' and 'ON' which is confusing me.

I've attached two image files, one shows the result of the query above the other one shows the result I needed.

Please any help or directions would be appreciated.

Thank You,

Dave

 

[MSAccessRookie]

Hi all,

I need assistance in putting 'UNION ALL' into the following SQL statement:

SELECT [zTBL_STATE_GROUP_TABLE 3].STATE, [zqry_nsw/act inconjunctiontable 3].[Total (in conjunction)], [zqry_inconjunction_QLD TABLE 3].[Total (in conjunction)], [zqry_inconjunction_SA/NT TABLE 3].[Total (in conjunction)]
FROM [zqry_nsw/act inconjunctiontable 3] RIGHT JOIN ([zqry_inconjunction_SA/NT TABLE 3] RIGHT JOIN ([zqry_inconjunction_QLD TABLE 3] RIGHT JOIN [zTBL_STATE_GROUP_TABLE 3] ON [zqry_inconjunction_QLD TABLE 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE) ON [zqry_inconjunction_SA/NT TABLE 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE) ON [zqry_nsw/act inconjunctiontable 3].STATE = [zTBL_STATE_GROUP_TABLE 3].STATE;

I know that the 'UNION ALL' should be placed after the 'FROM', but here it has 'RIGHT JOIN' and 'ON' which is confusing me.

I've attached two image files, one shows the result of the query above the other one shows the result I needed.

Please any help or directions would be appreciated.

Thank You,

Dave

UNION ALL goes in between COMPLETE Queries that have similar results (the same number of columns of the same type in the same order). Something like this:

[B]Select Table1.a, Table1.b, Table2.c From Table1 [/B]
[B]   LEFT JOIN Table2 on Table1.PrimaryKey=Table2.PrimaryKey[/B]
[B]UNION ALL[/B]
[B]Select Table3.d, Table3.e, Table4.f From Table3 [/B]
[B]   LEFT JOIN Table4 on Table3.PrimaryKey=Table4.PrimaryKey;[/B]

In your case, the code is RED represents what I believe the be the COMPLETE Query.

NOTE that these observations do not necessarily represent issues that need to be changes, and rather represent issues that, if changes, could help you in the long run.

Observation #1: Although the explanation regarding UNION ALL is accurate, I am not sure that UNION ALL is what you need. Try it and get back to us with the results.

Observation #2: Your Table/Column names are all very long which makes it more difficult to understand and debug, and increases the possibility that a misspelled name could create an issue.

Observation #3: Your Table/Column names are Access unfriendly in that they contain special characters (" ", "/", "(", ")") that should be avoided due to incompatibility issues.

 

[3dman]

Re: Help with inserting a 'UNION ALL' *SQL query* [RESOLVED]

I've got it working!

What I did was delete a table that had 'RIGHT JOIN' to it and did a 'UNION ALL' on each table, but this resulted in deleting the STATES column (which wasn't needed anyways). I've attached the result I got.

The code is shown below:

SELECT [zqry_nsw/act inconjunctiontable 3].[Total (in conjunction)]
FROM [zqry_nsw/act inconjunctiontable 3]
UNION ALL
SELECT [zqry_inconjunction_QLD TABLE 3].[Total (in conjunction)]
FROM [zqry_inconjunction_QLD TABLE 3]
UNION ALL
SELECT [zqry_inconjunction_SA/NT TABLE 3].[Total (in conjunction)]
FROM [zqry_inconjunction_SA/NT TABLE 3]
UNION ALL
SELECT [zqry_inconjunction_VIC/TAS TABLE 3].[Total (in conjunction)]
FROM [zqry_inconjunction_VIC/TAS TABLE 3]
UNION ALL SELECT [zqry_inconjunction_WA TABLE 3].[Total (in conjunction)]
FROM [zqry_inconjunction_WA TABLE 3];

Thank You for your suggestions. I will keep in mind to have better naming conventions for my table names next time.