I use these 2 queries and then paste the results into excel...

Q1
SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

Q2
SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
[X]/OCC_Vol AS PHLX
FROM Query3
ORDER BY OCC_Vol DESC;

The excel spreadsheet looks something like this:

Trade Date 4/27/2011

Result Q1 OCC_Vol BOX_Vol
17,439,351 513,649



Result Q2 underlying OCC_Vol BOX_Vol
SPY 2,212,187 46,832
SLV 1,285,651 46,377
STX 983,631 809
IWM 419,558 6,851

I type in trade date manually.

Can a form be used so I do not need to copy/paste into excel ?

Thanks you in advance.

On Thu, 28 Apr 2011 08:31:17 -0700 (PDT), carl <(E-Mail Removed)> wrote:

> I use these 2 queries and then paste the results into excel...
>
>Q1
>SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
>[X]/OCC_Vol AS PHLX
>FROM Query3
>ORDER BY OCC_Vol DESC;
>
>Q2
>SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
>[X]/OCC_Vol AS PHLX
>FROM Query3
>ORDER BY OCC_Vol DESC;
>
>The excel spreadsheet looks something like this:
>
>Trade Date 4/27/2011
>
> Result Q1 OCC_Vol BOX_Vol
> 17,439,351 513,649
>
>
>
>Result Q2 underlying OCC_Vol BOX_Vol
> SPY 2,212,187 46,832
> SLV 1,285,651 46,377
> STX 983,631 809
> IWM 419,558 6,851
>
>I type in trade date manually.
>
>Can a form be used so I do not need to copy/paste into excel ?
>
>Thanks you in advance.

I'd use File... Export from the query, or use the TransferSpreadsheet method
in VBA. A Form will not help much - you can copy and pasted from a form, but
it's no easier than doing so from a query datasheet. The TransferSpreadsheet
can write to a named range, which it looks like you'll want.
--

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

On Apr 28, 1:39*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 28 Apr 2011 08:31:17 -0700 (PDT), carl <cmieda...@msn.com> wrote:
> > *I use these 2 queries and then paste the results into excel...
>
> >Q1
> >SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
> >[X]/OCC_Vol AS PHLX
> >FROM Query3
> >ORDER BY OCC_Vol DESC;
>
> >Q2
> >SELECT Query3.OCC_Vol, [B] AS BOX_Vol, ([B]/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, [I]/OCC_Vol AS ISE, [Q]/OCC_Vol AS NOM,
> >[X]/OCC_Vol AS PHLX
> >FROM Query3
> >ORDER BY OCC_Vol DESC;
>
> >The excel spreadsheet looks something like this:
>
> >Trade Date *4/27/2011
>
> > * *Result Q1 * * * *OCC_Vol * * * * BOX_Vol
> > * * * * * * 17,439,351 * * *513,649
>
> >Result Q2 * underlying * * * OCC_Vol * * * * BOX_Vol
> > * *SPY * * *2,212,187 * * * 46,832
> > * *SLV * * *1,285,651 * * * 46,377
> > * *STX * * *983,631 * * * * 809
> > * *IWM * * *419,558 * * * * 6,851
>
> >I type in trade date manually.
>
> >Can a form be used so I do not need to copy/paste into excel ?
>
> >Thanks you in advance.
>
> I'd use File... Export from the query, or use the TransferSpreadsheet method
> in VBA. A Form will not help much - you can copy and pasted from a form, but
> it's no easier than doing so from a query datasheet. The TransferSpreadsheet
> can write to a named range, which it looks like you'll want.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -

Thanks John.

Would you be able to show me how I would do this ?

For example, the file I want to save the results to is C:\market.xls.

In that spreadsheet, I would like the results from Q1 to be in range
C3:M4 and for Q2 B8:M3000.

Also. If it is possible, I would like the cell format to be 2 decimal
percentage in E4:M3000.

If this question is posted in the wrong area could you tell me what
area a post like this belongs.

Thanks again.

On Thu, 28 Apr 2011 11:46:34 -0700 (PDT), carl <(E-Mail Removed)> wrote:

>Thanks John.
>
>Would you be able to show me how I would do this ?
>
>For example, the file I want to save the results to is C:\market.xls.
>
>In that spreadsheet, I would like the results from Q1 to be in range
>C3:M4 and for Q2 B8:M3000.
>
>Also. If it is possible, I would like the cell format to be 2 decimal
>percentage in E4:M3000.
>
>If this question is posted in the wrong area could you tell me what
>area a post like this belongs.

I have very little experience interfacing with Excel.

You may be aware that Microsoft stopped supporting these newsgroups last year.
They are no longer hosted on Microsoft's servers, or accessible from the
"help" feature of the program; as a result traffic is way, way down, both in
terms of people asking questions and people answering. See my .sig for some
alternative forums. My good friends at UtterAccess are very expert and may be
able to walk you through the process.
--

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

On Apr 28, 4:19*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 28 Apr 2011 11:46:34 -0700 (PDT), carl <cmieda...@msn.com> wrote:
> >Thanks John.
>
> >Would you be able to show me how I would do this ?
>
> >For example, the file I want to save the results to is C:\market.xls.
>
> >In that spreadsheet, I would like the results from Q1 to be in range
> >C3:M4 and for Q2 B8:M3000.
>
> >Also. If it is possible, I would like the cell format to be 2 decimal
> >percentage in E4:M3000.
>
> >If this question is posted in the wrong area could you tell me what
> >area a post like this belongs.
>
> I have very little experience interfacing with Excel.
>
> You may be aware that Microsoft stopped supporting these newsgroups last year.
> They are no longer hosted on Microsoft's servers, or accessible from the
> "help" feature of the program; as a result traffic is way, way down, bothin
> terms of people asking questions and people answering. See my .sig for some
> alternative forums. My good friends at UtterAccess are very expert and may be
> able to walk you through the process.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com


Thanks.