Report

[Andre Adams]

I have not gotten a good answer for the below. Can someone pls help? This
is actually a post from earlier that I'm reposting. Can you let me know if
I'm being clear enough?

"I have a very interesting question. I have some data that I need to
summarize into a query. First of all, let me begin by telling you that I
have a table that stores all of my daily trade data with the following fields.

1. Account Number
2. Account Name
3. Symbol
4. Description of the Symbol
5. Settlement Date
6. Tran (Either Buy or Sell)
7. Shrs/Contr (Shares or Quantity)
8. Trade Price (Price on the trade)
9. Commission Amount

From this table I want to pull the following:

1. Total Commission (for the month)
2. Total shares (for the quarter)
3. Average cost per share (Commission divided by the shares)
4. Total Commission (Year to date)
5. Total Number of shares (Year to date)
6. Average cost per share (Year to date)

It seems, to me, that this is a complicated equation. I don't have the
slightest idea as to how to begin. Can somebody help?"

 

[Ken Sheridan]

While it would be possible to return all the aggregated values with a single
query using subqueries, there is no point doing so as its simpler to do the
aggregation in a report, so:

1. Start by creating a query which returns all rows for the year to date.
In the query include computed columns TradeQuarter and TradeMonth, using the
DatePart function with the Settlement Date as its date argument.

2. Base a report on the query.

3. Group the report first on TradeQuarter and then on TradeMonth, giving
each group a group footer. In the TradeQuarter footer aggregate the shares
and in the TradeMonth footer aggregate commission values.

4. Give the report a report footer and in the footer aggregate the
commission/shares (I'm assuming this is YTD though you don't say so), shares
and commission.

Ken Sheridan
Stafford, England