Risk Cube

[Rob]

I have a current database that creates "Risk Cubes" Currently it produces 1
cube per risk. I would like to create a cumulative Risk Cube. From an
earlier post I have created a query that tells me how many of a certain Risk
Exposure Level I have.

Example:
ID, Probability, Impact, Exposure

1, .50, 4, 2.00
2, .50, 4, 2.00

The query returns

Exposure Value, Exposure Count
2.00, 2

Now I have a Risk Cube form setup and ready to go. I would like to get the
Exposure Count number to show up on the appropriate square on the cube. I am
guessing that this would be an IIF statement:

Here is the current statement that I am using.

=IIf([Spiral 1 Cumulative Risks]!Expr1 Between 1.22 And 1.81," «Expr»
[Spiral 1 Cumulative Risks]![ExposureCount] «Expr»","")

Note:
[Spiral 1 Cumulative Risks] is the query name and !Expr1 is the column name
for Exposure value.

Note[Spiral 1 Cumulative Risks]![ExposureCount] «Expr» is the column for
Exposure Count.

I made these formulas via Expression Builder.

Any guidance is greatly appreciated.

Thanks
Rob

 

[Rob]

Damon,
Thanks for the post. You are correct each cell will have a different
ranges. I have tried the IIF statement that I posted earlier but with no
success. The problem is the same a grocery store might have.

I have 20 widgets. I want to have the total number of a particular product
placed on a form. In this case 20 needs to show up on a section of a
graphic. The 20 was produced by a query. How do you capture that number and
have it placed on the graphic in a particular place. IIF statement should
work but I fear I am missing a part of the statement.

Thanks

Rob

IIF first has the expression to evaluate, then the value if true, then the
value if false. In your case, you want a blank cell if false.

=IIf([Spiral 1 Cumulative Risks]!Expr1 Between 1.22 And 1.81, [Spiral 1
Cumulative Risks]![ExposureCount] ,"")

This would have to change for each cell in your cube. To make the expression
easier to debug and read, you might want to put two textboxes on your form
with visible properties= false, with their control source your query values.
So your expressions would be:
=IIf([text1] between 1.22 and 1.81, [text2],"")

Damon

I have a current database that creates "Risk Cubes" Currently it produces
1
cube per risk. I would like to create a cumulative Risk Cube. From an
earlier post I have created a query that tells me how many of a certain
Risk
Exposure Level I have.

Example:
ID, Probability, Impact, Exposure

1, .50, 4, 2.00
2, .50, 4, 2.00

The query returns

Exposure Value, Exposure Count
2.00, 2

Now I have a Risk Cube form setup and ready to go. I would like to get
the
Exposure Count number to show up on the appropriate square on the cube. I
am
guessing that this would be an IIF statement:

Here is the current statement that I am using.

=IIf([Spiral 1 Cumulative Risks]!Expr1 Between 1.22 And 1.81," «Expr»
[Spiral 1 Cumulative Risks]![ExposureCount] «Expr»","")

Note:
[Spiral 1 Cumulative Risks] is the query name and !Expr1 is the column
name
for Exposure value.

Note[Spiral 1 Cumulative Risks]![ExposureCount] «Expr» is the column for
Exposure Count.

I made these formulas via Expression Builder.

Any guidance is greatly appreciated.

Thanks
Rob

 

[Rob]

I guess I am not following what you are saying. I tried and nothing happens.
I guess I am confused by the IIF statement.

Would it be better to use the Count function and reach back to the table I
use as the source?

Rob

Did you try my second method? It works for me!

Damon

Damon,
Thanks for the post. You are correct each cell will have a different
ranges. I have tried the IIF statement that I posted earlier but with no
success. The problem is the same a grocery store might have.

I have 20 widgets. I want to have the total number of a particular
product
placed on a form. In this case 20 needs to show up on a section of a
graphic. The 20 was produced by a query. How do you capture that number
and
have it placed on the graphic in a particular place. IIF statement
should
work but I fear I am missing a part of the statement.

Thanks

Rob

IIF first has the expression to evaluate, then the value if true, then
the
value if false. In your case, you want a blank cell if false.

=IIf([Spiral 1 Cumulative Risks]!Expr1 Between 1.22 And 1.81, [Spiral 1
Cumulative Risks]![ExposureCount] ,"")

This would have to change for each cell in your cube. To make the
expression
easier to debug and read, you might want to put two textboxes on your
form
with visible properties= false, with their control source your query
values.
So your expressions would be:
=IIf([text1] between 1.22 and 1.81, [text2],"")

Damon


I have a current database that creates "Risk Cubes" Currently it
produces
1
cube per risk. I would like to create a cumulative Risk Cube. From an
earlier post I have created a query that tells me how many of a certain
Risk
Exposure Level I have.

Example:
ID, Probability, Impact, Exposure

1, .50, 4, 2.00
2, .50, 4, 2.00

The query returns

Exposure Value, Exposure Count
2.00, 2

Now I have a Risk Cube form setup and ready to go. I would like to get
the
Exposure Count number to show up on the appropriate square on the cube.
I
am
guessing that this would be an IIF statement:

Here is the current statement that I am using.

=IIf([Spiral 1 Cumulative Risks]!Expr1 Between 1.22 And 1.81," «Expr»
[Spiral 1 Cumulative Risks]![ExposureCount] «Expr»","")

Note:
[Spiral 1 Cumulative Risks] is the query name and !Expr1 is the column
name
for Exposure value.

Note[Spiral 1 Cumulative Risks]![ExposureCount] «Expr» is the column
for
Exposure Count.

I made these formulas via Expression Builder.

Any guidance is greatly appreciated.

Thanks
Rob