Union Query

[nike2000]

Hi,
Could you let me know what is wrong with this Union Query?
Thanks,

SELECT Bundlet_No, ReferenceEntity
FROM Tbl_Trades
WHERE (((ReferenceEntity) Is Not Null))
GROUP BY Tbl_Trades.Bundlet_No

UNION SELECT Bundlet_Mirror, Reference Entity
FROM Tbl_Trades
WHERE (((ReferenceEntity) Is Not Null))
GROUP BY Tbl_Trades.Bundlet_Mirror;

 

[SarahG]

It depends on what you want this to do.

At a glance, it looks like something is missing. When you specify a 'GROUP BY', then you must either include the Reference Entity like so..

GROUP BY Tbl_Trades.Bundlet_Mirror, [Reference Entity];

which will remove any duplicates

or carry out some sort of aggregate function on Reference Entity, e.g.
SELECT Bundlet_No, COUNT([Reference Entity])
to return the number of Reference Entity fields

 

[PHV]

Are Bundlet_No and Bundlet_Mirror of same type (text or numeric) ?
And an unwanted space here:
Reference Entity

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nike2000]

Thanks for getting back to me guys.

What I am trying to do is merge the results of Bundlet_No and Bundlet_Mirror. But I would like to exclude all of the records that the Reference Entity is null.

Thanks again.