Query Help Needed

C

carl

My database looks like this:

underlying option symbol exchange PC Vol BD Vol MM Vol date Product
AAPL AAPL W 1043 304 2447 20110414 OSTK
AAPL AAPL C 41301 5415 42566 20110414 OSTK
AAPL AAPL I 41308 5252 37964 20110414 OSTK
AAPL AAPL Q 43155 3042 20397 20110414 OSTK
AAPL AAPL X 33980 9266 38232 20110414 OSTK
AAPL AAPL1C O 103 3 58 20110414 OSKF
AAPL AAPL A 67167 3415 61854 20110414 OSTK
AAPL AAPL P 17292 2116 17702 20110414 OSTK
AAPL AAPL Z 22441 3384 9763 20110414 OSTK
AAPL AAPL B 7864 2334 6984 20110414 OSTK


I need a query which will for a given date (prompted), produce a table/
report like this:

underlying Total A B C I O P Q W X Z
AAPL 548152 24% 3% 16% 15% 0% 7% 12% 1% 15% 6%


Where Total is the sum for all exchanges PC Vol, BD Vol, and MM Vol
for the underlying. My database has more than one underlying. Also
need this total to reflect only Product=OSTK.

For A,B,C,I etc (exchange), the value is their respective sum of PC
Vol, BD, Vol, and MM Vol divided by Total - thus the % share of the
total.

Is this possible to do in a query ?

Thank you inadvance.
 
J

John Spencer

That will require TWO queries to generate - a crosstab query to get the values
and a select query to calculate the percentages. Or one CROSSTAB query and a
report.

Query one:
TRANSFORM Sum(Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM Vol],0))
SELECT Underlying
, Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol]) as TotalVol
FROM [SomeTable]
GROUP BY Underlying
PIVOT Exchange in ("A","B","C","I","O","P","Q","W","X","Z")

Query Two
SELECT Underlying, TotalVol
, [A]/TotalVol as A_
, /TotalVol as B_
....
, [Z]/TotalVol as Z_
FROM [CrosstabQuery]

Or you could do it all in one query using IIF statements.
SELECT Underlying
, SUM(IIF([Exchange]="A",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as [A]

, SUM(IIF([Exchange]="B",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as

, ...
FROM [SomeTable]

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

John Spencer

Forgot the GROUP BY clause in the last example. Also should have noted that I
expect the SUM of each Vol to never return Null in the expression (Sum([PC
Vol]) + SUM([BD Vol]) + Sum([MM Vol])). Also that the expression in total
would never equal zero.


SELECT Underlying
, SUM(IIF([Exchange]="A",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as [A]

, SUM(IIF([Exchange]="B",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as

, ...
FROM [SomeTable]
GROUP BY Underlying


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

carl

That will require TWO queries to generate - a crosstab query to get the values
and a select query to calculate the percentages.  Or one CROSSTAB queryand a
report.

Query one:
TRANSFORM Sum(Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM Vol],0))
SELECT Underlying
, Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol]) as TotalVol
FROM [SomeTable]
GROUP BY Underlying
PIVOT Exchange in ("A","B","C","I","O","P","Q","W","X","Z")

Query Two
SELECT Underlying, TotalVol
, [A]/TotalVol as A_
, /TotalVol as B_
...
, [Z]/TotalVol as Z_
FROM [CrosstabQuery]

Or you could do it all in one query using IIF statements.
SELECT Underlying
, SUM(IIF([Exchange]="A",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as [A]

, SUM(IIF([Exchange]="B",Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM
Vol],0),0))/(Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol])) as

  , ...
FROM [SomeTable]

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

My database looks like this:
Click to expand...
underlying option symbol   exchange        PC Vol  BD Vol  MM Vol  date    Product
AAPL                     AAPL      W       1043    304     2447    20110414        OSTK
AAPL                     AAPL      C       41301   5415    42566   20110414        OSTK
AAPL                     AAPL      I       41308   5252    37964   20110414        OSTK
AAPL                     AAPL      Q       43155   3042    20397   20110414        OSTK
AAPL                     AAPL      X       33980   9266    38232   20110414        OSTK
AAPL                     AAPL1C    O       103     3       58      20110414        OSKF
AAPL                     AAPL      A       67167   3415    61854   20110414        OSTK
AAPL                     AAPL      P       17292   2116    17702   20110414        OSTK
AAPL                     AAPL      Z       22441   3384    9763    20110414        OSTK
AAPL                     AAPL      B       7864    2334    6984    20110414        OSTK
Click to expand...
I need a query which will for a given date (prompted), produce a table/
report like this:
Click to expand...
underlying Total   A       B       C       I       O       P       Q       W       X       Z
AAPL       548152  24%     3%      16%     15%     0%      7%      12%     1%      15%     6%
Click to expand...
Where Total is the sum for all exchanges PC Vol, BD Vol, and MM Vol
for the underlying. My database has more than one underlying. Also
need this total to reflect only Product=OSTK.
Click to expand...
For A,B,C,I etc (exchange), the value is  their respective sum of PC
Vol, BD, Vol, and MM Vol divided by Total - thus the % share of the
total.
Click to expand...
Is this possible to do in a query ?
Click to expand...
Thank you inadvance.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Thank you for your help.

I have this query based on your suggestion for the 2 query solution...

TRANSFORM Sum(Nz([PC Vol],0)+Nz([BD Vol],0)+Nz([MM Vol],0)) AS Expr1
SELECT OCC_Data.underlying, Sum([PC Vol])+Sum([BD Vol])+Sum([MM Vol])
AS TotalVol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]))
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","O","P","Q","W","X","Z");

Which generates the raw numbers.

I need the second query that will produce the result below - I can't
seem to get the query you revommended to work. Notice that the
TotalVol is the actual number generated by the first query while the
other values (A,B...Z) are the % share of the underlying.

underlying TotalVol A B C I O P Q W X Z
AAPL 548152 24% 3% 16% 15% 0% 7% 12% 1% 15% 6%

Thanks again.
 
J

John Spencer

The following should work

SELECT Underlying, TotalVol, [A]/TotalVol as AShare
, /TotalVol as BShare
, [C]/TotalVol as CShare
....
FROM [NameOfCrossTabQuery]

If that doesn't work, explain what results you do get. If you get an error,
what is the error.

Copy the SQL of query that is "not working" and post it.


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

That will require TWO queries to generate - a crosstab query to get the values
and a select query to calculate the percentages. Or one CROSSTAB query and a
report.

Query one:
TRANSFORM Sum(Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM Vol],0))
SELECT Underlying
, Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol]) as TotalVol
FROM [SomeTable]
GROUP BY Underlying
PIVOT Exchange in ("A","B","C","I","O","P","Q","W","X","Z")

Query Two
SELECT Underlying, TotalVol
, [A]/TotalVol as A_
, /TotalVol as B_
...
, [Z]/TotalVol as Z_
FROM [CrosstabQuery]
Click to expand...
Click to expand...
 
C

carl

The following should work

SELECT Underlying, TotalVol, [A]/TotalVol as AShare
, /TotalVol as BShare
, [C]/TotalVol as CShare
...
FROM [NameOfCrossTabQuery]

If that doesn't work, explain what results you do get.  If you get an error,
what is the error.

Copy the SQL of query that is "not working" and post it.

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

That will require TWO queries to generate - a crosstab query to get the values
and a select query to calculate the percentages.  Or one CROSSTAB query and a
report.
Query one:
TRANSFORM Sum(Nz([PC Vol],0) + Nz([BD Vol],0) + Nz([MM Vol],0))
SELECT Underlying
, Sum([PC Vol]) + SUM([BD Vol]) + Sum([MM Vol]) as TotalVol
FROM [SomeTable]
GROUP BY Underlying
PIVOT Exchange in ("A","B","C","I","O","P","Q","W","X","Z")
Query Two
SELECT Underlying, TotalVol
, [A]/TotalVol as A_
, /TotalVol as B_
...
, [Z]/TotalVol as Z_
FROM [CrosstabQuery]- Hide quoted text -
Click to expand...
Click to expand...


- Show quoted text -
Click to expand...


Thank you again. Worked fantastic.

Can the query be modified so that the results "[A]/TotalVol as A_" can
be expressed as a percentage with no decimals ?
 
J

John Spencer

Multiply the calculation by 100 and then truncate it with INT to strip off the
decimal portion or use CLng to round it.

Int(/TotalVol * 100)
or
CLng(/TotalVol * 100)


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

- Show quoted text -
Click to expand...

Thank you again. Worked fantastic.

Can the query be modified so that the results "[A]/TotalVol as A_" can
be expressed as a percentage with no decimals ?
Click to expand...
 
C

carl

Multiply the calculation by 100 and then truncate it with INT to strip off the
decimal portion or use CLng to round it.

   Int(/TotalVol * 100)
or
   CLng(/TotalVol * 100)

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




Thank you again. Worked fantastic.
Click to expand...
Can the query be modified so that the results "[A]/TotalVol as A_" can
be expressed as a percentage with no decimals ?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Thanks Again. That worked well.

Now the user has requested that the there be 2 decimals. Can the query
control the number of decimals ?
 
J

John Spencer

Try multiplying by 10000 and dividing by 100

Or try using the format function

Format(,"0.00%")

That should return a string that is formatted appropriately.
Format(.592341123,"0.00%") return 59.23%

You can't do math on the string, but if you only need to display it the Format
should work well.


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

Multiply the calculation by 100 and then truncate it with INT to strip off the
decimal portion or use CLng to round it.

Int(/TotalVol * 100)
or
CLng(/TotalVol * 100)

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




- Show quoted text -
Click to expand...
Thank you again. Worked fantastic.
Click to expand...
Can the query be modified so that the results "[A]/TotalVol as A_" can
be expressed as a percentage with no decimals ?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Thanks Again. That worked well.

Now the user has requested that the there be 2 decimals. Can the query
control the number of decimals ?
Click to expand...
 
Top