subtotal on one item only

[lake2212]

On a report, I list a Stock name, Symbol, Security Type, Option Description,
and Number of Shares. Would it be possible to subtotal only the "Stock"
Security Type, and only show the subtotal for that Security Type, then go on
to list the other records without subtotals?

Example - Here would be sample data:

Stock name Symbol Security Type Option Desc Number of Shares
American Exp AXP Stock 500
American Exp AXP Stock 200
American Exp AXP Stock 25
American Exp AXP Call Jan-10-20 -15
American Exp AXP Put Apr-09-25.5 20
American Exp AXP Call Jan-11-50 -35

Can I get the report to look like this:
Stock Name Symbol Security Type Option Desc Number of Shares
American Exp AXP Stock 725
Call Jan-10-20
-15
Put Apr-09-25.5 20
Call Jan-11-50
-35

Thanks for your help.
Julie

 

[lake2212]

Correction:Can I get the report to look like this:

Stock Name Symbol Sec Type Desc #Shares
American Exp AXP Stock 725
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie

 

[KARL DEWEY]

Try this query --
SELECT lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type], IIf([Sec
Type]="Stock",Null,[Desc]) AS Descrip, Sum(lake2212.[#Shares]) AS
[SumOf#Shares]
FROM lake2212
GROUP BY lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type],
IIf([Sec Type]="Stock",Null,[Desc]);

 

[lake2212]

I should have posted this in the New User area. I am not following you. I
already have a query and report built. Is this something that I would put
into an existing query? If so, where does this code go? What is the
significance of "lake2212" here? Thanks for your help.
Julie

Try this query --
SELECT lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type], IIf([Sec
Type]="Stock",Null,[Desc]) AS Descrip, Sum(lake2212.[#Shares]) AS
[SumOf#Shares]
FROM lake2212
GROUP BY lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type],
IIf([Sec Type]="Stock",Null,[Desc]);

--
KARL DEWEY
Build a little - Test a little

Correction:Can I get the report to look like this:

Stock Name Symbol Sec Type Desc #Shares
American Exp AXP Stock 725
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie

 

[KARL DEWEY]

I did not know the name of your table so I gave it "lake2212".
What I posted is known as SQL, a langage Access uses in a query.

To use it open a new query in design view, do not select a table, click on
menu VIEW - SQL View, paste in the SQL window that opens. Replace "Lake2212"
with your table name. Run the query to see if it gives you the results you
are looking for.

OR
In query design view select your table, drag each field from the table to
the Field row of the grid. Click on the Sigma symbol (looks like an "M" on
it's side).

Change the Group By below [#Shares] to Sum. In place of [Desc] type --
Descrip: IIf([Sec Type]="Stock",Null,[Desc])

Run the query to see if it gives you the results you are looking for.
--
KARL DEWEY
Build a little - Test a little

I should have posted this in the New User area. I am not following you. I
already have a query and report built. Is this something that I would put
into an existing query? If so, where does this code go? What is the
significance of "lake2212" here? Thanks for your help.
Julie

Try this query --
SELECT lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type], IIf([Sec
Type]="Stock",Null,[Desc]) AS Descrip, Sum(lake2212.[#Shares]) AS
[SumOf#Shares]
FROM lake2212
GROUP BY lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type],
IIf([Sec Type]="Stock",Null,[Desc]);

--
KARL DEWEY
Build a little - Test a little

Correction:Can I get the report to look like this:

Stock Name Symbol Sec Type Desc #Shares
American Exp AXP Stock 725
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie

 

[John W. Vinson/MVP]

Correction:Can I get the report to look like this:

Stock Name Symbol Sec Type Desc #Shares
American Exp AXP Stock 725
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Just a suggestion: don't use Desc as a fieldname. It's the keyword to
sort in DESC (descending order) and Access *will* get confused. Descr
would work fine though.

 

[lake2212]

I'm not sure this will work for me. I was going to try your option #2, but
the field DESC is actually a combination of two fields [Option Date]&[Option
Strike Price]. Also the #SHARES is actually an expression:
Sum(Nz([SharesBought],0)-Nz([SharesSold],0))

Would there still be a way to make this work? Thanks.
Julie

I did not know the name of your table so I gave it "lake2212".
What I posted is known as SQL, a langage Access uses in a query.

To use it open a new query in design view, do not select a table, click on
menu VIEW - SQL View, paste in the SQL window that opens. Replace "Lake2212"
with your table name. Run the query to see if it gives you the results you
are looking for.

OR
In query design view select your table, drag each field from the table to
the Field row of the grid. Click on the Sigma symbol (looks like an "M" on
it's side).

Change the Group By below [#Shares] to Sum. In place of [Desc] type --
Descrip: IIf([Sec Type]="Stock",Null,[Desc])

Run the query to see if it gives you the results you are looking for.
--
KARL DEWEY
Build a little - Test a little

I should have posted this in the New User area. I am not following you. I
already have a query and report built. Is this something that I would put
into an existing query? If so, where does this code go? What is the
significance of "lake2212" here? Thanks for your help.
Julie

Try this query --
SELECT lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type], IIf([Sec
Type]="Stock",Null,[Desc]) AS Descrip, Sum(lake2212.[#Shares]) AS
[SumOf#Shares]
FROM lake2212
GROUP BY lake2212.[Stock Name], lake2212.Symbol, lake2212.[Sec Type],
IIf([Sec Type]="Stock",Null,[Desc]);

--
KARL DEWEY
Build a little - Test a little


:

Correction:Can I get the report to look like this:

Stock Name Symbol Sec Type Desc #Shares
American Exp AXP Stock 725
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie