Creating conditional totals in report footers

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

Guest

Hi,
I have to create conditional totals (count and sum) for different segments
in a report
footer. The criteria need to use to determine whether to count or sum is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I cannot
determine how to include the criteria for a Yes Securitized values and No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
 
Counting conditions in a report is the same as summing the absolute value of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )
 
Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


Duane Hookom said:
Counting conditions in a report is the same as summing the absolute value of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


David said:
Hi,
I have to create conditional totals (count and sum) for different segments
in a report
footer. The criteria need to use to determine whether to count or sum is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I cannot
determine how to include the criteria for a Yes Securitized values and No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
 
No. Using the expression that I provided will count the number of records
where the condition is true. Your expression will count all of the records.

If you can't get your head around this, tell us whether you want to sum a
value or count records.

--
Duane Hookom
MS Access MVP


David said:
Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


Duane Hookom said:
Counting conditions in a report is the same as summing the absolute value
of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


David said:
Hi,
I have to create conditional totals (count and sum) for different
segments
in a report
footer. The criteria need to use to determine whether to count or sum
is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I
cannot
determine how to include the criteria for a Yes Securitized values and
No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Hi,
Nope, all set with understanding your logic. However, brings up another
question.
For certain reports I have to count the records (will use your suggestion
already incorporated it and it worked, thanks again) while for other reports
I also have to sum a value(amount) where the SecuritizedInd is Yes/No.

So for example, 6 Securitized Item - 250,000

Should I think about using an IIF statement for this one?

--
David


Duane Hookom said:
No. Using the expression that I provided will count the number of records
where the condition is true. Your expression will count all of the records.

If you can't get your head around this, tell us whether you want to sum a
value or count records.

--
Duane Hookom
MS Access MVP


David said:
Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


Duane Hookom said:
Counting conditions in a report is the same as summing the absolute value
of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


Hi,
I have to create conditional totals (count and sum) for different
segments
in a report
footer. The criteria need to use to determine whether to count or sum
is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I
cannot
determine how to include the criteria for a Yes Securitized values and
No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
To get the sum of [Amount] where SecuritizedInd = "YES", use:

=Sum(Abs([SecuritizedInd]="YES") * [Amount])

--
Duane Hookom
MS Access MVP
--

David said:
Hi,
Nope, all set with understanding your logic. However, brings up another
question.
For certain reports I have to count the records (will use your suggestion
already incorporated it and it worked, thanks again) while for other
reports
I also have to sum a value(amount) where the SecuritizedInd is Yes/No.

So for example, 6 Securitized Item - 250,000

Should I think about using an IIF statement for this one?

--
David


Duane Hookom said:
No. Using the expression that I provided will count the number of records
where the condition is true. Your expression will count all of the
records.

If you can't get your head around this, tell us whether you want to sum a
value or count records.

--
Duane Hookom
MS Access MVP


David said:
Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


:

Counting conditions in a report is the same as summing the absolute
value
of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


Hi,
I have to create conditional totals (count and sum) for different
segments
in a report
footer. The criteria need to use to determine whether to count or
sum
is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I
cannot
determine how to include the criteria for a Yes Securitized values
and
No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help Duane.

I tried your suggestion and it didn't work. I think it had more to do with
my explanation than your solution. I did however get the answer I wanted
using the following formula
=IIf([SecuritizedInd]="NO",Sum([OrigCredit]))

So thanks again for your timely and helpful responses.
--
David


Duane Hookom said:
To get the sum of [Amount] where SecuritizedInd = "YES", use:

=Sum(Abs([SecuritizedInd]="YES") * [Amount])

--
Duane Hookom
MS Access MVP
--

David said:
Hi,
Nope, all set with understanding your logic. However, brings up another
question.
For certain reports I have to count the records (will use your suggestion
already incorporated it and it worked, thanks again) while for other
reports
I also have to sum a value(amount) where the SecuritizedInd is Yes/No.

So for example, 6 Securitized Item - 250,000

Should I think about using an IIF statement for this one?

--
David


Duane Hookom said:
No. Using the expression that I provided will count the number of records
where the condition is true. Your expression will count all of the
records.

If you can't get your head around this, tell us whether you want to sum a
value or count records.

--
Duane Hookom
MS Access MVP


Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


:

Counting conditions in a report is the same as summing the absolute
value
of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


Hi,
I have to create conditional totals (count and sum) for different
segments
in a report
footer. The criteria need to use to determine whether to count or
sum
is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I
cannot
determine how to include the criteria for a Yes Securitized values
and
No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Good morning Duane,

I had a look again at my report and it wasn't coming up with the correct
answer. I retried your suggestion and it worked great.

Thanks again,

David


David said:
Thanks for your help Duane.

I tried your suggestion and it didn't work. I think it had more to do with
my explanation than your solution. I did however get the answer I wanted
using the following formula
=IIf([SecuritizedInd]="NO",Sum([OrigCredit]))

So thanks again for your timely and helpful responses.
--
David


Duane Hookom said:
To get the sum of [Amount] where SecuritizedInd = "YES", use:

=Sum(Abs([SecuritizedInd]="YES") * [Amount])

--
Duane Hookom
MS Access MVP
--

David said:
Hi,
Nope, all set with understanding your logic. However, brings up another
question.
For certain reports I have to count the records (will use your suggestion
already incorporated it and it worked, thanks again) while for other
reports
I also have to sum a value(amount) where the SecuritizedInd is Yes/No.

So for example, 6 Securitized Item - 250,000

Should I think about using an IIF statement for this one?

--
David


:

No. Using the expression that I provided will count the number of records
where the condition is true. Your expression will count all of the
records.

If you can't get your head around this, tell us whether you want to sum a
value or count records.

--
Duane Hookom
MS Access MVP


Thanks for the quick response.
So does that also hold true for the function Count?
Count(Abs([SecuritizedInd]="YES")


--
David


:

Counting conditions in a report is the same as summing the absolute
value
of
the condition:
=Sum(Abs([SecuritizedInd]=True) )
or if the field is truely a text field:
=Sum(Abs([SecuritizedInd]="YES") )

--
Duane Hookom
MS Access MVP


Hi,
I have to create conditional totals (count and sum) for different
segments
in a report
footer. The criteria need to use to determine whether to count or
sum
is
based on a field called SecuritizedInd, with a value of YES or NO.

When I try to use a control in the footer, either Count or Sum, I
cannot
determine how to include the criteria for a Yes Securitized values
and
No
Securitized values.

For instance, this does not work
=IIf([SecuritizedInd]='YES',Count([SecuritizedInd]))

Does anyone have a suggestion for this item?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top