subtotal on a group in a report

I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.

Thanks

cmk wrote:

I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.


I think the Type header/footer text box expression would be:
=originalshares - Sum(transactionshares)

--
Marsh
MVP [MS Access]

when I put in the formula below it returns a blank field. Any ideas what I
may be doing wrong

thanks
"cmk" wrote:

I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.

Thanks

cmk wrote:
when I put in the formula below it returns a blank field. Any ideas what I
may be doing wrong


"cmk" wrote:
I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.


Assuming you replaced my placeholder names with your own
**field** names (not control names), what expression did you
actually use?

Where did you put it?

Could you also provide some demonstative sample data from
the query along with the names of each field.

--
Marsh
MVP [MS Access]

I placed the following in stock type footer (the first footer (there are 3
other footers)):

=[CERTIFICATE (TABLE).NumberOfShares]-Sum([TRANSACTION
(TABLE).NumberOfShares])

My end result I want to be the sum of certificate [CERTIFICATE
(TABLE).NumberOfShares] by stock type

StockType Original Purchase Trans Amt Cert #
Common Stock 100 -5 bbb
Common Stock 100 -2 bbb
Common Stock 300 -50 ddd
subtotal 400
Preferred Stock 200 -50 fff
Preferred Stock 300 -25 ggg
Preferred Stock 200 -15 fff
subtotal 500

I am running the report off of 2 tables a detail transaction table and a
certificate table. In the above sample I am looking for the subtotal amount.


Let me know if there is something else you need -- thanks for your help

"Marshall Barton" wrote:

cmk wrote:
when I put in the formula below it returns a blank field. Any ideas what I
may be doing wrong


"cmk" wrote:
I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.


Assuming you replaced my placeholder names with your own
**field** names (not control names), what expression did you
actually use?

Where did you put it?

Could you also provide some demonstative sample data from
the query along with the names of each field.

--
Marsh
MVP [MS Access]

OK, that's very helpful information.

The syntax looks very suspicious. It looks like
CERTIFICATE (TABLE) and TRANSACTION (TABLE)
are the names of the tables in the query and that both
tables have a field named NumberOfShares. If so, the
references should be:

=[CERTIFICATE (TABLE)].NumberOfShares - Sum([TRANSACTION
(TABLE)].NumberOfShares)

But, I don't see how you get the subtotals in your sample so
the expression may not be right.
--
Marsh
MVP [MS Access]


cmk wrote:
I placed the following in stock type footer (the first footer (there are 3
other footers)):

=[CERTIFICATE (TABLE).NumberOfShares]-Sum([TRANSACTION
(TABLE).NumberOfShares])

My end result I want to be the sum of certificate [CERTIFICATE
(TABLE).NumberOfShares] by stock type

StockType Original Purchase Trans Amt Cert #
Common Stock 100 -5 bbb
Common Stock 100 -2 bbb
Common Stock 300 -50 ddd
subtotal 400
Preferred Stock 200 -50 fff
Preferred Stock 300 -25 ggg
Preferred Stock 200 -15 fff
subtotal 500

I am running the report off of 2 tables a detail transaction table and a
certificate table. In the above sample I am looking for the subtotal amount.


cmk wrote:
when I put in the formula below it returns a blank field. Any ideas what I
may be doing wrong


"cmk" wrote:
I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.


"Marshall Barton" wrote:
Assuming you replaced my placeholder names with your own
**field** names (not control names), what expression did you
actually use?

Where did you put it?

Could you also provide some demonstative sample data from
the query along with the names of each field.

My subtotals are the sum of the original purchase for each unique certificate
number. The original purchase is a field in the certificate table and in
this table it is only in the file once, but when I tie the certificate table
and transaction table together it may show up several times because each
certificate may have several transactions.

I think I might have found a solution by putting
=Sum([CERTIFICATE (TABLE).NumberOfShares])
in the first header (certificatetable.stocktype) then putting this field in
the first footer (certificatetable.stocktype)

It seems to be giving me a total for each stock type footer

thanks

"Marshall Barton" wrote:

OK, that's very helpful information.

The syntax looks very suspicious. It looks like
CERTIFICATE (TABLE) and TRANSACTION (TABLE)
are the names of the tables in the query and that both
tables have a field named NumberOfShares. If so, the
references should be:

=[CERTIFICATE (TABLE)].NumberOfShares - Sum([TRANSACTION
(TABLE)].NumberOfShares)

But, I don't see how you get the subtotals in your sample so
the expression may not be right.
--
Marsh
MVP [MS Access]


cmk wrote:
I placed the following in stock type footer (the first footer (there are 3
other footers)):

=[CERTIFICATE (TABLE).NumberOfShares]-Sum([TRANSACTION
(TABLE).NumberOfShares])

My end result I want to be the sum of certificate [CERTIFICATE
(TABLE).NumberOfShares] by stock type

StockType Original Purchase Trans Amt Cert #
Common Stock 100 -5 bbb
Common Stock 100 -2 bbb
Common Stock 300 -50 ddd
subtotal 400
Preferred Stock 200 -50 fff
Preferred Stock 300 -25 ggg
Preferred Stock 200 -15 fff
subtotal 500

I am running the report off of 2 tables a detail transaction table and a
certificate table. In the above sample I am looking for the subtotal amount.


cmk wrote:
when I put in the formula below it returns a blank field. Any ideas what I
may be doing wrong


"cmk" wrote:
I haven't been able to find a way to subtotal a group correctly in a report.

My report is using a query that has two tables joined. First table is a
Stock Type Table and the second table is a Transaction Table.

The first table has the original # of shares and the second table has the
number of shares sold on a specific date.

I have four headers in the report. The first header stock type is the one I
want a subtotal on. I want to be able to subtotal the original # of shares
purchased from the First Table and put it in the First Footer. I need to
make sure that if I have several transactions in table 2 it doesn't add the
original shares several times but just once.


"Marshall Barton" wrote:
Assuming you replaced my placeholder names with your own
**field** names (not control names), what expression did you
actually use?

Where did you put it?

Could you also provide some demonstative sample data from
the query along with the names of each field.