Union Query

[carl]

I use this Union Query...

SELECT 'Overall' As Underlying, * FROM Share_Total_BOX_Vol AND
UNION ALL Select * FROM Share_Symbol_BOX_Vol
ORDER BY OCC_Vol DESC;

I wanted to include the results of this query

SELECT Daily_Big3.OCC_Vol, Daily_Big3.B AS BOX_Vol, (/OCC_Vol) AS
BOX, [A]/OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS,
[C]/OCC_Vol AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/
OCC_Vol AS NOM, [X]/OCC_Vol AS PHLX
FROM Daily_Big3
ORDER BY OCC_Vol DESC;

I would like these results to be labled as "Big3" in Underlying firld.

I tried all sorts of ways but to no avail.

Thank you in advance.

 

[Rob Parker]

I'm surprised that your current Union query doesn't give an error due to the
'AND' at the end of the first line. And I'm also surprised that the second
SELECT portion of your union query is returning one more field - since each
SELECT portion of a union query must return the same number of fields - than
the first portion, so that you don't have to define the 'Underlying' field
content for it and the other fields are subsequently corresponding
correctly. I'm assuming, therefore, that your 'Share_Symbol_BOX_Vol' has an
additional (first) field with the data which you want to place in the
'Underlying' fields of your Union query.

But this is what I suggest (given that the number of fields returned from
your 'Daily_Big3' query is the same (and in the same order) as the number of
fields returned from your 'Share_Total_BOX_Vol' query/table:

SELECT 'Overall' As Underlying, * FROM Share_Total_BOX_Vol
UNION ALL
Select * FROM Share_Symbol_BOX_Vol
UNION ALL
SELECT "Big3" ,Daily_Big3.OCC_Vol, Daily_Big3.B AS BOX_Vol, (/OCC_Vol) AS
BOX, [A]/OCC_Vol AS AMEX, [P]/OCC_Vol AS ARCA, [Z]/OCC_Vol AS BATS,
[C]/OCC_Vol AS CBOE, [W]/OCC_Vol AS C2, /OCC_Vol AS ISE, [Q]/OCC_Vol AS
NOM,
[X]/OCC_Vol AS PHLX FROM Daily_Big3
ORDER BY OCC_Vol DESC;

Note, particularly, that the 'AS ...' clauses in the final SELECT clause
will have no effect (although I've left them in the SELECT clause I
cut/pasted from you post); the field names in a Union query are defined by
the first SELECT clause in the Union. If that's what you want the fields in
your Union query to retunr, and it's not the field names from your first
SELECT clause, you'll need to change the order of the SELECT clauses. Also,
there can only be a single ORDER BY clause, at the end of the union, and it
applies to all records from each of the SELECT clauses; if you want/need to
group them by the 'Underlying' field, you need to include this in the ORDER
BY clause, as the first field in that clause.

Hope this is clear enough, and it helps,

Rob