Sum by day

[Guest]

Hey Guys,

How do I sum by day? I have the following fieds in my query.

Account Name
Shares/Contract
Commission Amount
Settlment Date

I'm trying to create a report, out of this, that says....
"Show me the the sums of the Shares/Contracts & Commission amount of each
individual account name between a defined Settlement Date."

Any help?

 

[Guest]

If you are going to do this in a report, use the report Sorting And Grouping.

 

[Guest]

Thanks for you prompt response Klatuu.

I'm trying to do it in query mode. When I did it in the report mode, it
showed me the Account name in the Header and then all other information
below. (i.e,

Jacobs Levy
120,526 (5 trades) $123,526.00

when I need it to show this way.

Account Name Shares/Contracts Commission Amount
Jacobs Levy 120,526 $123,526.00

Please Advise.

Andre Adams

 

[Guest]

Use a Totals query.
In the Query Builder, select the Totals icon on the tool bar. It is the one
that looks like the Greek letter Sigma.

Then you will need to put the fields in the following order, sorted
Ascending on the first 3. Select Group By for all the fields except
Commission Amount

Account Name
Settlment Date
Shares/Contract
Commission Amount

If you need to show only a range of dates, put the necessary criteria on the
Settlement Date field.

Note, this will cause the report to run more slowly, but if it is not a
large table, you may not notice the difference.

 

[Guest]

Hey Klatuu,

That's exactly the step that stumped me. This query will bring all the
Account Names and their total'd trades by settlement date. How would I get
this report to total by month? So, I want you to total all the trades for
this Account Name for the Month of January. That query works fine to see
what my individual accounts have done by each day, how would I see this
"combined" information by month?

Andre Adams

 

[Guest]

Create a calculated field in your query that will return the month of the
settlement date and include that in the query in place if the settlement date:

SettlementMonth: Month([Settlement Date])