Creatng If Statements with several groups

  • Thread starter Thread starter dcrqueens
  • Start date Start date
D

dcrqueens

Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" Or
[Disposition]="Region"))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
 
i don't think i'm following you. if you want the same information, only at
the report level rather than the group level, why do you need different
logic in the IIf() functions? you should be able to copy the calculated
controls from the group level to the report level; the report level controls
will return calculations for all records in the report.

hth
 
The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)
 
Hi Duane,

Thank you for the response. I guess it is still a problem because how do I
make sure that I it is going to give me the infomrmation just for the regions
I am looking for. For instance if I want to cfombins region 2,4,5,6 and find
out only the ones that are approved being
Approved-Conforming,Approved-Exceptions,Approved-Workout,Approved Not
Accepted, how do I make a statement for this?

Duane Hookom said:
The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)

--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" ))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
You should be storing some value in your tables that a user could edit that
would identify which regions and dispositions to include. Whenever possible
store your business calculation info in tables. There should be something
stored along with the "2,4,5,6" records and not the others.
--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Hi Duane,

Thank you for the response. I guess it is still a problem because how do I
make sure that I it is going to give me the infomrmation just for the regions
I am looking for. For instance if I want to cfombins region 2,4,5,6 and find
out only the ones that are approved being
Approved-Conforming,Approved-Exceptions,Approved-Workout,Approved Not
Accepted, how do I make a statement for this?

Duane Hookom said:
The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)

--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" ))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Yes I do have seperate fields. I have one for region, and one for
disposition. There are several regions within a state that is why I was
trying to add them all together so that I could find out what the total
amounts are.

Duane Hookom said:
You should be storing some value in your tables that a user could edit that
would identify which regions and dispositions to include. Whenever possible
store your business calculation info in tables. There should be something
stored along with the "2,4,5,6" records and not the others.
--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Hi Duane,

Thank you for the response. I guess it is still a problem because how do I
make sure that I it is going to give me the infomrmation just for the regions
I am looking for. For instance if I want to cfombins region 2,4,5,6 and find
out only the ones that are approved being
Approved-Conforming,Approved-Exceptions,Approved-Workout,Approved Not
Accepted, how do I make a statement for this?

Duane Hookom said:
The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)

--
Duane Hookom
Microsoft Access MVP


:

Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" ))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Can you respond to "There should be something stored along with the "2,4,5,6"
records and not the others"? Do you have a field that identifies these
particular records?

--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Yes I do have seperate fields. I have one for region, and one for
disposition. There are several regions within a state that is why I was
trying to add them all together so that I could find out what the total
amounts are.

Duane Hookom said:
You should be storing some value in your tables that a user could edit that
would identify which regions and dispositions to include. Whenever possible
store your business calculation info in tables. There should be something
stored along with the "2,4,5,6" records and not the others.
--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Hi Duane,

Thank you for the response. I guess it is still a problem because how do I
make sure that I it is going to give me the infomrmation just for the regions
I am looking for. For instance if I want to cfombins region 2,4,5,6 and find
out only the ones that are approved being
Approved-Conforming,Approved-Exceptions,Approved-Workout,Approved Not
Accepted, how do I make a statement for this?

:

The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)

--
Duane Hookom
Microsoft Access MVP


:

Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" ))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Duane thank you SOOOOOOOOOO much it worked. I created seperate tables for the
regions and the apprvoal codes. it was so much easier than the way I was
doing it. I have been at his thing for over 1 week. Thanks gan for helping
out.

dcrqueens said:
Yes I do have seperate fields. I have one for region, and one for
disposition. There are several regions within a state that is why I was
trying to add them all together so that I could find out what the total
amounts are.

Duane Hookom said:
You should be storing some value in your tables that a user could edit that
would identify which regions and dispositions to include. Whenever possible
store your business calculation info in tables. There should be something
stored along with the "2,4,5,6" records and not the others.
--
Duane Hookom
Microsoft Access MVP


dcrqueens said:
Hi Duane,

Thank you for the response. I guess it is still a problem because how do I
make sure that I it is going to give me the infomrmation just for the regions
I am looking for. For instance if I want to cfombins region 2,4,5,6 and find
out only the ones that are approved being
Approved-Conforming,Approved-Exceptions,Approved-Workout,Approved Not
Accepted, how do I make a statement for this?

:

The same expressions you use in group footers should work in report footers.

BTW: you should have a table of unique dispositions with a field the
identifies/stores whether the disposition is approved (1) or not approved
(0). You can then add the disposition table to your report's record source
and Sum([Approved]) or Sum(Abs(Approved-1))

You can use the following to get the sum of the approved loans.
=Sum(Amount * Approved)

--
Duane Hookom
Microsoft Access MVP


:

Can anyone help with combining group totals into a new IIF statement. I have
the following totals in my group footer:

1. (This give me the total # of loan approved in each
group)=Sum(Abs([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Override" ))

2. (Provides the total # of loans declined in each
group)=Sum(Abs([Disposition]="Declined" Or [DIsposition]="Withdrawn" Or
[Disposition]="Reversed" Or [Disposition]="Counter-Not Accepted" Or
[Disposition]="Pending Approval"))

3. (Provides the total # of loans decisioned for the region) =Count([Region
#])

4. (Provides total $ amount approved)
=Sum(IIf([Disposition]="Approved-Conforming" Or
[Disposition]="Approved-Exceptions" Or [Disposition]="Approved-Workout" Or
[Disposition]="Approved Not Accepted" Or [Disposition]="Counter-Not Accepted"
Or [Disposition]="Override" Or [Disposition]="Region",[Amount],0))

5. (Provides the total $ amount declined) =Sum(IIf([Disposition]="declined"
Or [Disposition]="Withdrawn" Or [Disposition]="Reversed" Or
[Disposition]="Pending Approval" Or [Disposition]="Counter-Not
Accepted",[Amount],0))

6. (Provides the total $ decisioned)=Sum([Amount])

All of these formulas work just fine for the groups. The problem that I now
have is that I want to know the sdame information as a grand total in the
report footer.. For instance Alabama has region 1,2,4,5,6 and I want to find
out how many of the loans are approved ( This can be several different
dispositions shown in item 1), declined and so on just as I did for group
totals. I have tried all week long and I have been unable to figure out how
to combine such a complex statement. Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top