Counting and summing in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this is going to be easy for most of you, but I'm stuck. I am trying
to build a report off a query. The query is basically just giving me the
sales for my team in a date range. I've built the report in the layout that
I want, but I need to do calculations now. I know I have to use a text box,
and type an expression in the control source, but I don't know what to time.
I have 7 different people that I need to calculate for. Here are the
calculations I'm needing:

Number of Total Sales in the query per person
Number of Total Lines in the query per person
Number of sales that have the value "Access","LD","DSL" in a cell
Total Monthly Revenue
Total Contract Value

The Label for the sales person is called [SSC]
The Label for the total lines is [Total Lines]
The Label for the access, ld, dsl value is [Comp Category]
Monthly Revenue is [NNI]
Total Contract Value is [TCV]

I tried doing this, IIF([SSC]="Name",count([SSC],0)) This counted every sale
because the persons name was in the list. I also tried
=sum(IIF([SSC]="Name",[NNI],0)) That didn't work either. If I made any
sense here, can you help?
 
Is your "Number...per person" located together in the report footer or do
you have person footers for each calculation?

Do you have some field names from the report's record source that you could
share with us?
 
I built the report in the Detail section. Do I need to do this in the Footer
Section?

Sales Title # of Sales Product 1 Product 2

Anthony (calculation) (calculation)
(calculation)
Donna (calculation) (calculation)
(calculation)
etc

We will be getting new sales people so I would like for the Names to be
lookup's if that's possible. Instead having having to go in every time I get
a new person, it would automatically add them to the report. The
calculations would count the number of sales in a date range for Anthony,
number of product 1's in a date range for anthony, etc. Same for Donna.

Duane Hookom said:
Is your "Number...per person" located together in the report footer or do
you have person footers for each calculation?

Do you have some field names from the report's record source that you could
share with us?

--
Duane Hookom
MS Access MVP


Matt said:
I know this is going to be easy for most of you, but I'm stuck. I am
trying
to build a report off a query. The query is basically just giving me the
sales for my team in a date range. I've built the report in the layout
that
I want, but I need to do calculations now. I know I have to use a text
box,
and type an expression in the control source, but I don't know what to
time.
I have 7 different people that I need to calculate for. Here are the
calculations I'm needing:

Number of Total Sales in the query per person
Number of Total Lines in the query per person
Number of sales that have the value "Access","LD","DSL" in a cell
Total Monthly Revenue
Total Contract Value

The Label for the sales person is called [SSC]
The Label for the total lines is [Total Lines]
The Label for the access, ld, dsl value is [Comp Category]
Monthly Revenue is [NNI]
Total Contract Value is [TCV]

I tried doing this, IIF([SSC]="Name",count([SSC],0)) This counted every
sale
because the persons name was in the list. I also tried
=sum(IIF([SSC]="Name",[NNI],0)) That didn't work either. If I made any
sense here, can you help?
Click to expand...
Click to expand...
 
We really don't have any idea what your query (fields and sample records)
look like. Do you mind sharing?

--
Duane Hookom
MS Access MVP
--

Matt said:
I built the report in the Detail section. Do I need to do this in the
Footer
Section?

Sales Title # of Sales Product 1 Product
2

Anthony (calculation) (calculation)
(calculation)
Donna (calculation) (calculation)
(calculation)
etc

We will be getting new sales people so I would like for the Names to be
lookup's if that's possible. Instead having having to go in every time I
get
a new person, it would automatically add them to the report. The
calculations would count the number of sales in a date range for Anthony,
number of product 1's in a date range for anthony, etc. Same for Donna.

Duane Hookom said:
Is your "Number...per person" located together in the report footer or do
you have person footers for each calculation?

Do you have some field names from the report's record source that you
could
share with us?

--
Duane Hookom
MS Access MVP


Matt said:
I know this is going to be easy for most of you, but I'm stuck. I am
trying
to build a report off a query. The query is basically just giving me
the
sales for my team in a date range. I've built the report in the layout
that
I want, but I need to do calculations now. I know I have to use a text
box,
and type an expression in the control source, but I don't know what to
time.
I have 7 different people that I need to calculate for. Here are the
calculations I'm needing:

Number of Total Sales in the query per person
Number of Total Lines in the query per person
Number of sales that have the value "Access","LD","DSL" in a cell
Total Monthly Revenue
Total Contract Value

The Label for the sales person is called [SSC]
The Label for the total lines is [Total Lines]
The Label for the access, ld, dsl value is [Comp Category]
Monthly Revenue is [NNI]
Total Contract Value is [TCV]

I tried doing this, IIF([SSC]="Name",count([SSC],0)) This counted every
sale
because the persons name was in the list. I also tried
=sum(IIF([SSC]="Name",[NNI],0)) That didn't work either. If I made
any
sense here, can you help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top