UNION Query

[ria.arora]

Please let me know how to use UNION query in MS Access. I have a table A with columns (RMName, AUM, AUA( and Table B with Columns (RMName, NNA, REV)

Table 1
RMName,  AUM, AUA 
James  10 2
David  20 6
Andrew  18 4
 
Table 2
RMName,  NNA, REV
James  6 3
Paul  3 1

I want the result to be displayed as:

RMName,  AUM, AUA NNA, REV 
James  10 2 6 3
David  20 6 0 0
Andrew  18 4 0 0
Paul  0 0 3 1

 

[ByteMyzer]

You need a union set between a query with Table 1 left-joined to Table 2 and a query with Table 1 right-joined to Table 2.

You might try something like the following:

SELECT [Table 1].RMName, [Table 1].AUM, [Table 1].AUA,
       Nz([Table 2].NNA, 0) AS NNA, Nz([Table 2].REV, 0) AS REV
FROM [Table 1]
LEFT JOIN [Table 2] ON [Table 1].RMName = [Table 2].RMName

UNION SELECT [Table 2].RMName, Nz([Table 1].AUM, 0), Nz([Table 1].AUA, 0),
             [Table 2].NNA, [Table 2].REV
FROM [Table 1]
RIGHT JOIN [Table 2] ON [Table 1].RMName = [Table 2].RMName;

 

[recyan]

An alternative, perhaps :

SELECT 
	qryDisinctRMNamesA.RMName, 
	Table1.RMName, 
	Nz([AUM],0) AS AUMNew, 
	Nz([AUA],0) AS AUANew, 
	Table2.RMName, 
	Nz([MNA],0) AS MNANew, 
	Nz([REV],0) AS REVNew
FROM 
	(
		(
			SELECT RMName FROM Table1
			UNION 
			SELECT RMName FROM Table2
		) 
		AS qryDisinctRMNamesA 
		LEFT JOIN 
		Table1 
		ON 
		qryDisinctRMNamesA.RMName = Table1.RMName
	) 
	LEFT JOIN 
	Table2 
	ON 
	qryDisinctRMNamesA.RMName = Table2.RMName;

Thanks

 

[ByteMyzer]

Good alternative solution, recyan. Would you care to explain, for ria.arora's benefit, what you did, and the concept of inline subqueries?

 

[recyan]

Hi ByteMyzer,

Would you care to explain, for ria.arora's benefit, what you did, and the concept of inline subqueries?

This is the very reason, I posted an alternative (even though you had posted the answer), so that you or some one else will comment on it technically ( advantages / dis-advantages ).

I usually (perhaps always) use the method I have posted, for the kind of problem that the OP has posted.

As for explaining things theoretically, my signature tells it all.

Thanks

 

[ria.arora]

Is there any other option get this result without using outer join. Because I need to cosolidate 4 different tables. And Primary Key in all the tables is BankerName and all Banker's will not exist in all the table e.g.

Table 1
BankerName
Rob
James
Tim

Table2
James
Bob
Paul

Table3
Andrew

Table4
James
Rita
King
Andrew
Chia
Chin
Chua

And all 4 tables will have different remaining columns like example given in above post.

Pls help.

 

[recyan]

Till some one comes along, was wondering,
1) do you not have a Banker's table with Banker's name as primary key ( BankerID rather than Name).
2) why are the details stored in 4 different tables ?



Edit :

perhaps, a joinless way,
The saved sub-query

qryTable1Table2

SELECT Table1.RMName, Table1.AUM, Table1.AUA, 0 AS MNA, 0 AS REV FROM Table1
UNION ALL 
SELECT Table2.RMName, 0 AS AUM, 0 AS AUA, Table2.MNA, Table2.REV FROM Table2;

The final query to run
qryqryTable1Table2

SELECT 
	qryTable1Table2.RMName, 
	Max(qryTable1Table2.AUM) AS MaxOfAUM, 
	Max(qryTable1Table2.AUA) AS MaxOfAUA, 
	Max(qryTable1Table2.MNA) AS MaxOfMNA, 
	Max(qryTable1Table2.REV) AS MaxOfREV
FROM 
	qryTable1Table2
GROUP BY 
	qryTable1Table2.RMName;

Above method is first time for me. Be sure to check it thoroughly or wait till some one trashes or ok's it.

Thanks

 

[vbaInet]

A LEFT JOIN between Table1 and Table2 via RMName with the Nz([FieldName], 0) will give the desired output (as shown). We're over complicating matters here.

By the way, the tables are not normalised.

 

[recyan]

A LEFT JOIN between Table1 and Table2 via RMName with the Nz([FieldName], 0) will give the desired output (as shown).

Perhaps, I have not understood things properly, will not Paul be left out in the results ( based on example given in OP's 1st post).

Thanks

 

[vbaInet]

Quite right recyan... thanks for pointing that out.

I would do a UNION between both just on RMName and then a LEFT JOIN to both tables afterwards.

 

[vbaInet]

Ah, wait... it looks like that's what you're doing already recyan. Apologies!

 

[recyan]

Apologies!

Now, you are really embarrassing me.

Thanks