Show totals for groups from a column in Page Footer

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

Guest

Hello -

I have a column in a report where the value is either Home, Business or
Both. These are derived from an input form that stores the values as 1, 2 or
3. The report has a formula for the control source as follows:
=IIf([LoanType]="1","Home",IIf([LoanType]="2","Business","Both")

I would like to get separate totals in the Page Footer for Home, Business
and Both and in addition would like a sum of all three together.

I do not want these to show as grouped in the report.

Is it possible to do this?
 
To get the Total Sum, for both, create a text box and in the control source
write
=Sum([AmountFieldName])

For Home
=Sum(IIf([LoanType]="1",[AmountFieldName],0))

For Business
=Sum(IIf([LoanType]="2",[AmountFieldName],0))

And you need to put the sum in the report footer
 
Hello Ofer:

Thanks for responding. You gave me a good place to start. I am posting
what I did end up using, for future readers. The first formula you gave me
worked, because the value of the field was "1"; therefore, when sum was used,
it merely added up the ones. Because the other two values were 2 and 3, Sum
did not work alone; I had to divide LoanType by 2 and 3, respectively;
otherwise it would total, e.g. 2+2+2.
The final total of all had to be Count, because otherwise, the above
scenario would have been encountered also.

Total of "Home"
=Sum(IIf([LoanType]="1",[LoanType],0))

Total of "Business"
=Sum(IIf([LoanType]="2",[LoanType]/2,0))

Total of "Both"
=Sum(IIf([LoanType]="3",[LoanType]/3,0))

Total of All
=Count([LoanType])

Thanks for starting me out on this!!
--
Sandy


Ofer said:
To get the Total Sum, for both, create a text box and in the control source
write
=Sum([AmountFieldName])

For Home
=Sum(IIf([LoanType]="1",[AmountFieldName],0))

For Business
=Sum(IIf([LoanType]="2",[AmountFieldName],0))

And you need to put the sum in the report footer

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Sandy said:
Hello -

I have a column in a report where the value is either Home, Business or
Both. These are derived from an input form that stores the values as 1, 2 or
3. The report has a formula for the control source as follows:
=IIf([LoanType]="1","Home",IIf([LoanType]="2","Business","Both")

I would like to get separate totals in the Page Footer for Home, Business
and Both and in addition would like a sum of all three together.

I do not want these to show as grouped in the report.

Is it possible to do this?
Click to expand...
Click to expand...
 
You could have use:

Total of "Home"
=Sum(IIf([LoanType]="1", 1, 0))

Total of "Business"
=Sum(IIf([LoanType]="2", 1, 0))

Total of "Both"
=Sum(IIf([LoanType]="3", 1, 0))

Total of All
=Count([LoanType])

What data type is the [LoanType]?

If it is numeric, you should remove the double quotes around the explicit
values 1, 2 & 3 ...

--
HTH
Van T. Dinh
MVP (Access)



Sandy said:
Hello Ofer:

Thanks for responding. You gave me a good place to start. I am posting
what I did end up using, for future readers. The first formula you gave
me
worked, because the value of the field was "1"; therefore, when sum was
used,
it merely added up the ones. Because the other two values were 2 and 3,
Sum
did not work alone; I had to divide LoanType by 2 and 3, respectively;
otherwise it would total, e.g. 2+2+2.
The final total of all had to be Count, because otherwise, the above
scenario would have been encountered also.

Total of "Home"
=Sum(IIf([LoanType]="1",[LoanType],0))

Total of "Business"
=Sum(IIf([LoanType]="2",[LoanType]/2,0))

Total of "Both"
=Sum(IIf([LoanType]="3",[LoanType]/3,0))

Total of All
=Count([LoanType])

Thanks for starting me out on this!!
--
Sandy


Ofer said:
To get the Total Sum, for both, create a text box and in the control
source
write
=Sum([AmountFieldName])

For Home
=Sum(IIf([LoanType]="1",[AmountFieldName],0))

For Business
=Sum(IIf([LoanType]="2",[AmountFieldName],0))

And you need to put the sum in the report footer

--
Please respond to the group if your question been answered or not, so
other
can refer to it.
Thank you and Good luck



Sandy said:
Hello -

I have a column in a report where the value is either Home, Business or
Both. These are derived from an input form that stores the values as
1, 2 or
3. The report has a formula for the control source as follows:

=IIf([LoanType]="1","Home",IIf([LoanType]="2","Business","Both")

I would like to get separate totals in the Page Footer for Home,
Business
and Both and in addition would like a sum of all three together.

I do not want these to show as grouped in the report.

Is it possible to do this?
Click to expand...
Click to expand...
Click to expand...
 
Hi Dinh:

Thanks for your response!

These are three option buttons. The 1, 2 and 3 are the numeric values. I
needed to get the Text equivalents to display in a report. [LoanType] is
text.

I don't see how your code would work under this circumstance. Am I missing
something? Let me know!
--
Sandy


Van T. Dinh said:
You could have use:

Total of "Home"
=Sum(IIf([LoanType]="1", 1, 0))

Total of "Business"
=Sum(IIf([LoanType]="2", 1, 0))

Total of "Both"
=Sum(IIf([LoanType]="3", 1, 0))

Total of All
=Count([LoanType])

What data type is the [LoanType]?

If it is numeric, you should remove the double quotes around the explicit
values 1, 2 & 3 ...

--
HTH
Van T. Dinh
MVP (Access)



Sandy said:
Hello Ofer:

Thanks for responding. You gave me a good place to start. I am posting
what I did end up using, for future readers. The first formula you gave
me
worked, because the value of the field was "1"; therefore, when sum was
used,
it merely added up the ones. Because the other two values were 2 and 3,
Sum
did not work alone; I had to divide LoanType by 2 and 3, respectively;
otherwise it would total, e.g. 2+2+2.
The final total of all had to be Count, because otherwise, the above
scenario would have been encountered also.

Total of "Home"
=Sum(IIf([LoanType]="1",[LoanType],0))

Total of "Business"
=Sum(IIf([LoanType]="2",[LoanType]/2,0))

Total of "Both"
=Sum(IIf([LoanType]="3",[LoanType]/3,0))

Total of All
=Count([LoanType])

Thanks for starting me out on this!!
--
Sandy


Ofer said:
To get the Total Sum, for both, create a text box and in the control
source
write
=Sum([AmountFieldName])

For Home
=Sum(IIf([LoanType]="1",[AmountFieldName],0))

For Business
=Sum(IIf([LoanType]="2",[AmountFieldName],0))

And you need to put the sum in the report footer

--
Please respond to the group if your question been answered or not, so
other
can refer to it.
Thank you and Good luck



:

Hello -

I have a column in a report where the value is either Home, Business or
Both. These are derived from an input form that stores the values as
1, 2 or
3. The report has a formula for the control source as follows:

=IIf([LoanType]="1","Home",IIf([LoanType]="2","Business","Both")

I would like to get separate totals in the Page Footer for Home,
Business
and Both and in addition would like a sum of all three together.

I do not want these to show as grouped in the report.

Is it possible to do this?
Click to expand...
Click to expand...
Click to expand...
 
Dinh -

Sorry. That was the first part of what I had to do. We're talking Sum
right now. I get what you're talking about! Thanks!
--
Sandy


Van T. Dinh said:
You could have use:

Total of "Home"
=Sum(IIf([LoanType]="1", 1, 0))

Total of "Business"
=Sum(IIf([LoanType]="2", 1, 0))

Total of "Both"
=Sum(IIf([LoanType]="3", 1, 0))

Total of All
=Count([LoanType])

What data type is the [LoanType]?

If it is numeric, you should remove the double quotes around the explicit
values 1, 2 & 3 ...

--
HTH
Van T. Dinh
MVP (Access)



Sandy said:
Hello Ofer:

Thanks for responding. You gave me a good place to start. I am posting
what I did end up using, for future readers. The first formula you gave
me
worked, because the value of the field was "1"; therefore, when sum was
used,
it merely added up the ones. Because the other two values were 2 and 3,
Sum
did not work alone; I had to divide LoanType by 2 and 3, respectively;
otherwise it would total, e.g. 2+2+2.
The final total of all had to be Count, because otherwise, the above
scenario would have been encountered also.

Total of "Home"
=Sum(IIf([LoanType]="1",[LoanType],0))

Total of "Business"
=Sum(IIf([LoanType]="2",[LoanType]/2,0))

Total of "Both"
=Sum(IIf([LoanType]="3",[LoanType]/3,0))

Total of All
=Count([LoanType])

Thanks for starting me out on this!!
--
Sandy


Ofer said:
To get the Total Sum, for both, create a text box and in the control
source
write
=Sum([AmountFieldName])

For Home
=Sum(IIf([LoanType]="1",[AmountFieldName],0))

For Business
=Sum(IIf([LoanType]="2",[AmountFieldName],0))

And you need to put the sum in the report footer

--
Please respond to the group if your question been answered or not, so
other
can refer to it.
Thank you and Good luck



:

Hello -

I have a column in a report where the value is either Home, Business or
Both. These are derived from an input form that stores the values as
1, 2 or
3. The report has a formula for the control source as follows:

=IIf([LoanType]="1","Home",IIf([LoanType]="2","Business","Both")

I would like to get separate totals in the Page Footer for Home,
Business
and Both and in addition would like a sum of all three together.

I do not want these to show as grouped in the report.

Is it possible to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top