Query Question /

[carl]

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDateyyyymmdd]) And ((OCC_Data.Product)
In ('OSTK','OIND')) And ((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY','NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

This section of the query...

((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY','NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))

Is used in several other queries.

Is there a way that I can set up one source "table" with these
symbols, and if I update that 'table' with add/deletes of the symbols
that all my queries will update accordingly ?

Thank you in advance.

 

[John W. Vinson]

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDateyyyymmdd]) And ((OCC_Data.Product)
In ('OSTK','OIND')) And ((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY','NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

This section of the query...

((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY','NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))

Is used in several other queries.

Is there a way that I can set up one source "table" with these
symbols, and if I update that 'table' with add/deletes of the symbols
that all my queries will update accordingly ?

Thank you in advance.

Sure. Just create a little table tblUnderlying with this field, one record
per; you might want other fields (such as a human-readable explanation of what
a BRKB is). Just join this table to OCC_DATA joining by "underlying" and it
will include those fields.

If there are other values of [underlying] which occur in OCC_DATA and aren't
wanted in this query but might be in others, you could add another field or
fields to tblUnderlying to select or deselect specific values.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[carl]

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDateyyyymmdd]) And ((OCC_Data.Product)
In ('OSTK','OIND')) And ((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

This section of the query...

((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))

Is used in several other queries.

Is there a way that I can set up one source "table" with these
symbols, and if I update that 'table' with add/deletes of the symbols
that all my queries will update accordingly ?

Thank you in advance.

Sure. Just create a little table tblUnderlying with this field, one record
per; you might want other fields (such as a human-readable explanation ofwhat
a BRKB is). Just join this table to OCC_DATA joining by "underlying" and it
will include those fields.

If there are other values of [underlying] which occur in OCC_DATA and aren't
wanted in this query but might be in others, you could add another field or
fields to tblUnderlying to select or deselect specific values.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks. I apologize in advance - I not sure I understand how to set-up
the sql. Could you provide a sample of the sql ?

 

[John Spencer]

Assuming a table named tblUnderlying with field name Underlying and one record
for each of the values in the list -
'MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF',
'EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP',
'BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY',
'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG'.

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data Inner JOIN tblUnderlying
ON OCC_Data.Underlying = tblUnderlying.Underlying
WHERE OCC_Data.date=[EnterDateyyyymmdd]
And OCC_Data.Product In ('OSTK','OIND')
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDateyyyymmdd]) And ((OCC_Data.Product)
In ('OSTK','OIND')) And ((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

This section of the query...

((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))

Is used in several other queries.

Is there a way that I can set up one source "table" with these
symbols, and if I update that 'table' with add/deletes of the symbols
that all my queries will update accordingly ?

Thank you in advance.

Sure. Just create a little table tblUnderlying with this field, one record
per; you might want other fields (such as a human-readable explanation of what
a BRKB is). Just join this table to OCC_DATA joining by "underlying" and it
will include those fields.

If there are other values of [underlying] which occur in OCC_DATA and aren't
wanted in this query but might be in others, you could add another field or
fields to tblUnderlying to select or deselect specific values.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks. I apologize in advance - I not sure I understand how to set-up
the sql. Could you provide a sample of the sql ?

 

[carl]

Assuming a table named tblUnderlying with field name Underlying and one record
for each of the values in the list -
'MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF',
'EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP',
'BX','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EW­Y',
'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG'.

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data Inner JOIN tblUnderlying
ON OCC_Data.Underlying = tblUnderlying.Underlying
WHERE OCC_Data.date=[EnterDateyyyymmdd]
And OCC_Data.Product In ('OSTK','OIND')
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I use this query...
TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDateyyyymmdd]) And ((OCC_Data.Product)
In ('OSTK','OIND')) And ((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY­'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
GROUP BY ""
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");
This section of the query...
((OCC_Data.underlying) In
('MA','BRKB','MJN','CIT','GDX','TBT','ACAS','MNX','TSO','FAS','TLT','RF','­­EEM','EWZ','XHB','EFA','XLV','FSLR','APWR','XL','KMP','PBR','DRYS','XLP','B­­X','DAL','GMCR','UAL','LDK','AKS','XLB','ENER','WLT','CSX','CLF','HK','EWY­'­,'NYX','FAZ','XOP','IOC','ATVI','FIS','AFL','TSL','KRE','FXE','HIG')))
Is used in several other queries.
Is there a way that I can set up one source "table" with these
symbols, and if I update that 'table' with add/deletes of the symbols
that all my queries will update accordingly ?
Thank you in advance.
Sure. Just create a little table tblUnderlying with this field, one record
per; you might want other fields (such as a human-readable explanationof what
a BRKB is). Just join this table to OCC_DATA joining by "underlying" and it
will include those fields.
If there are other values of [underlying] which occur in OCC_DATA and aren't
wanted in this query but might be in others, you could add another field or
fields to tblUnderlying to select or deselect specific values.
--
              John W. Vinson [MVP]
  Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
  and see alsohttp://www.utteraccess.com-Hide quoted text -
- Show quoted text -

Thanks. I apologize in advance - I not sure I understand how to set-up
the sql. Could you provide a sample of the sql ?- Hide quoted text -

- Show quoted text -

Thank you