Query Question

[carl]

I am using 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) Between [BeginDate] And [EndDate]) AND
((OCC_Data.Product) In ('OSTK','OIND')) AND ((OCC_Data.underlying) Not
In ('VIX','SPX','OEX','DJX')))
GROUP BY date
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

I would lke to change the query so that is does not list out each date
- but instead sums all the days aonto one record.

Thank you in advance.

 

[John Spencer]

Remove the Group By clause

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) Between [BeginDate] And [EndDate]) AND
((OCC_Data.Product) In ('OSTK','OIND'))
AND ((OCC_Data.underlying) Not In ('VIX','SPX','OEX','DJX')))
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

 

[carl]

Remove the Group By clause

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) Between [BeginDate] And [EndDate]) AND
((OCC_Data.Product) In ('OSTK','OIND'))
AND ((OCC_Data.underlying) Not In ('VIX','SPX','OEX','DJX')))
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

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) Between [BeginDate] And [EndDate]) AND
((OCC_Data.Product) In ('OSTK','OIND')) AND ((OCC_Data.underlying) Not
In ('VIX','SPX','OEX','DJX')))
GROUP BY date
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");- Hidequoted text -

- Show quoted text -

Thanks. I tried this...

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) Between [BeginDate] And [EndDate]) AND
((OCC_Data.Product) In ('OSTK','OIND')) AND ((OCC_Data.underlying) Not
In ('VIX','SPX','OEX','DJX')))
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X");

But could not save the query - "Syntax Error (missing operator) .....

Any Thoughts ?

 

[John Spencer]

Assumption: BeginDate and EndDate are supposed to be parameter prompts. If
so add the following to the beginning of the SQL. In addition, since Date is
a reserved word, surround it with square brackets.

Parameters [BeginDate] DateTime, [EndDate] DateTime;

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] Between [BeginDate] And [EndDate]
AND OCC_Data.Product In ('OSTK','OIND')
AND OCC_Data.underlying Not In ('VIX','SPX','OEX','DJX')
PIVOT OCC_Data.exchange In ("B","A","P","Z","C","W","I","Q","X")

If that fails, then I am stumped, as I don't see any syntax errors.

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