FIRST QUESTION NOT CLEAR..COMPLICATED EXPRESSION IN REPORT

  • Thread starter Thread starter ReportTrouble
  • Start date Start date
R

ReportTrouble

OK my first question did not make sense... Here I go Again
I have a complicated query which shows the 'actions' of one person and I
need a total score for the 'action' however it is not just a simple sum
because some actions are worth more than other.

EXAMPLE
Tom has 2 total actions (2 records): LINK A with a total CLICK COUNT of 1
and LINK B with a total click count of 2.
Tom's Score is 3 because each click count is worth 1 ([1+2]x1)=3

Bob is a bit more complicated he has a total of 2 actions as well (2
records): LINK A with a CLICK COUNT of 2 and LINK B with a total click count
of 1 and 'requested an email'
Bob's total score is 8 because when a person requests an email it is worth 5
([1+2]x1+5)=8

I need the total score calculated automatically for 300+ contacts..is it
possible and if yes how?
 
WHERE are you trying to get the total score? If you are using a group footer
in a report, you can add a new control and use a formula like the one below as
the control's source. This examples assumes that your FIELDS are named Click
Count and Request for Info.

=Sum([Click Count]) + Sum(IIF[Request for Info] = "Requested an Email",5,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank You John
This espression works if the Request for Info field and the Click count are
filled out but not of one of the fields are null (nothing in them)...do you
know how to fix that?

John Spencer MVP said:
WHERE are you trying to get the total score? If you are using a group footer
in a report, you can add a new control and use a formula like the one below as
the control's source. This examples assumes that your FIELDS are named Click
Count and Request for Info.

=Sum([Click Count]) + Sum(IIF[Request for Info] = "Requested an Email",5,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
OK my first question did not make sense... Here I go Again
I have a complicated query which shows the 'actions' of one person and I
need a total score for the 'action' however it is not just a simple sum
because some actions are worth more than other.

EXAMPLE
Tom has 2 total actions (2 records): LINK A with a total CLICK COUNT of 1
and LINK B with a total click count of 2.
Tom's Score is 3 because each click count is worth 1 ([1+2]x1)=3

Bob is a bit more complicated he has a total of 2 actions as well (2
records): LINK A with a CLICK COUNT of 2 and LINK B with a total click count
of 1 and 'requested an email'
Bob's total score is 8 because when a person requests an email it is worth 5
([1+2]x1+5)=8

I need the total score calculated automatically for 300+ contacts..is it
possible and if yes how?
Click to expand...
Click to expand...
 
Try using the NZ function to force zero if the Sum is Null

=Nz(Sum([Click Count]),0) + Nz(Sum(IIF[Request for Info] = "Requested an
Email",5,0),0)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

WHERE are you trying to get the total score? If you are using a group
footer in a report, you can add a new control and use a formula like the
one below as the control's source. This examples assumes that your
FIELDS are named Click Count and Request for Info.

=Sum([Click Count]) + Sum(IIF[Request for Info] = "Requested an Email",5,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
OK my first question did not make sense... Here I go Again
I have a complicated query which shows the 'actions' of one person and
I need a total score for the 'action' however it is not just a simple
sum because some actions are worth more than other.

EXAMPLE
Tom has 2 total actions (2 records): LINK A with a total CLICK COUNT
of 1 and LINK B with a total click count of 2. Tom's Score is 3
because each click count is worth 1 ([1+2]x1)=3

Bob is a bit more complicated he has a total of 2 actions as well (2
records): LINK A with a CLICK COUNT of 2 and LINK B with a total click
count of 1 and 'requested an email'
Bob's total score is 8 because when a person requests an email it is
worth 5 ([1+2]x1+5)=8

I need the total score calculated automatically for 300+ contacts..is
it possible and if yes how?
Click to expand...
Click to expand...
 
Back
Top