Create an index based on multiple criteria

[kidkosmo2]

Ok...here's what I'm trying to acheive:

I am trying to create a form which will calculate who's signature is
required based on several criteria. The three cases for the
signatures are:
District Sales Manager (DSM) only
DSM & Managing Director (MD)
DSM, MD, & Sales Director (SD)

There are three factors used to determine the required signatures.
So, there are three text boxes on my form. I have named those
controls as follows:
txtCM (Contribution Margin) [intCM]
txtRV (Revenue) [intRV]
txtCst (Cost) [intCST]

And here are the different scenarios:
1. If intCM > 30 and intRV > $5000 then DSM Only
2. If intCM > 30 and intRV < $5000 and intCST <$10000 then DSM Only
3. If intCM > 30 and intRV < $5000 and intCST >$10000 then DSM & MD

4. If intCM >0 but <30, intRV > $5000, and intCST < $5000 then DSM
Only
5. If intCM >0 but <30, intRV > $5000, and intCST > $5000 then DSM &
MD
6. If intCM >0 but <30, intRV < $5000, and intCST < $5000 then DSM
Only
7. If intCM >0 but <30, intRV < $5000, and intCST > $5000 then DSM &
MD

8. If intCM < 0 (negative margin) then DSM, MD, & SD

My idea was to create a command button that will create an index
number based on each scenario which will return the same value for the
signature(s) required. For instance:
Scenario 1, 2, 4, & 6 return one value which I can then assign as DSM
Only
Scenario 3, 5, & 7 return one value which I can then assign as DSM &
MD
and, of course #8 stands alone with another value for all three
signatures.

I've tried a variety of If..Then statements to assign different
indexes to each criteria such as:

If intCM > 30 then
intCMIndex=5 (as an example)
Elseif intCM < 0 then
intCMIndex=1
Else
intCMIndex=3
End if

and so on for the other two values and then a final index of:
intSigIndex = intCMIndex + intRVIndex + intCSTIndex

But I can figure out how to get them to add up to a single value to
represent each of the signature scenarios (perhaps this would be a
good question for my old Calculus professor...I didn't do so great in
that class). Plus I just think there's got to be a better way! I'm
thinking a series of CASE statements would work better, but I don't
have any experience working with those.

I apologive for the extensive post. Any help would be appreciated!

 

[kidkosmo]

Ok...here's what I'm trying to acheive:

I am trying to create a form which will calculate who's signature is
required based on several criteria. The three cases for the
signatures are:
District Sales Manager (DSM) only
DSM & Managing Director (MD)
DSM, MD, & Sales Director (SD)

There are three factors used to determine the required signatures.
So, there are three text boxes on my form. I have named those
controls as follows:
txtCM (Contribution Margin) [intCM]
txtRV (Revenue) [intRV]
txtCst (Cost) [intCST]

And here are the different scenarios:
1. If intCM > 30 and intRV > $5000 then DSM Only
2. If intCM > 30 and intRV < $5000 and intCST <$10000 then DSM Only
3. If intCM > 30 and intRV < $5000 and intCST >$10000 then DSM & MD

4. If intCM >0 but <30, intRV > $5000, and intCST < $5000 then DSM
Only
5. If intCM >0 but <30, intRV > $5000, and intCST > $5000 then DSM &
MD
6. If intCM >0 but <30, intRV < $5000, and intCST < $5000 then DSM
Only
7. If intCM >0 but <30, intRV < $5000, and intCST > $5000 then DSM &
MD

8. If intCM < 0 (negative margin) then DSM, MD, & SD

My idea was to create a command button that will create an index
number based on each scenario which will return the same value for the
signature(s) required. For instance:
Scenario 1, 2, 4, & 6 return one value which I can then assign as DSM
Only
Scenario 3, 5, & 7 return one value which I can then assign as DSM &
MD
and, of course #8 stands alone with another value for all three
signatures.

I've tried a variety of If..Then statements to assign different
indexes to each criteria such as:

If intCM > 30 then
intCMIndex=5 (as an example)
Elseif intCM < 0 then
intCMIndex=1
Else
intCMIndex=3
End if

and so on for the other two values and then a final index of:
intSigIndex = intCMIndex + intRVIndex + intCSTIndex

But I can figure out how to get them to add up to a single value to
represent each of the signature scenarios (perhaps this would be a
good question for my old Calculus professor...I didn't do so great in
that class). Plus I just think there's got to be a better way! I'm
thinking a series of CASE statements would work better, but I don't
have any experience working with those.

I apologive for the extensive post. Any help would be appreciated!

Correction to statement : I CAN'T figure out how to get them to ad
up....

 

[tina]

well, i really don't get the whole index idea. what you have are three
people who may need to sign the "whatever it is". there are specific
criteria for when each person needs to sign, independent of the other sigs
required in that situation. i'd probably approach it that way.

but first, i notice there are gaps in the value ranges. for instance,
intCM > 0
is covered, and
intCM < 0
is covered, but what happens when
intCM = 0
?
and > 30 or < 30 are covered, but again what happens when
intCM = 30
?

and again, intRV > 5000 and intRV < 5000 are covered, but what about intRV =
5000? ditto for intCST > and < 5000, and intCST > and < 10000.

for the sake of analysis, let's say that the equations should read as

1. If intCM > 30 and intRV >= $5000 then DSM Only
2. If intCM > 30 and intRV < $5000 and intCST < $10000 then DSM Only
3. If intCM > 30 and intRV < $5000 and intCST >= $10000 then DSM & MD

4. If intCM between 0 and 30, intRV >= $5000, and intCST < $5000 then DSM
Only
5. If intCM between 0 and 30, intRV >= $5000, and intCST >= $5000 then DSM &
MD
6. If intCM between 0 and 30, intRV < $5000, and intCST < $5000 then DSM
Only
7. If intCM between 0 and 30, intRV < $5000, and intCST >= $5000 then DSM &
MD

8. If intCM < 0 (negative margin) then DSM, MD, & SD

assuming that every "whatever it is" will have available values for each of
the three criteria, then the DSM sig is a no-brainer because it's required
in every situation. likewise the SD sig because it's required only when
intCM is < 0 without regard for any other criteria.

so the only issue is when to require the MD sig. further analysis shows that
when
intCM between 0 and 30, and intCST >= 5000, then the MD sig is required
regardless of the intRV value.
and when
intCM > 30, intRV < 5000, and intCST >= 10000, then the MD sig is required.

that makes for a fairly simple IF statement, as

If intCM < 0 Then
' all 3 sigs are required
ElseIf (intCM >= 0 And intCM <= 30 And _
intCST >= 5000) Or _
(intCM > 30 And intRV < 5000 And _
intCST >= 10000) Then
' DSM and MD sig are required
Else
' only DSM sig is required
End If

hth

Ok...here's what I'm trying to acheive:

I am trying to create a form which will calculate who's signature is
required based on several criteria. The three cases for the
signatures are:
District Sales Manager (DSM) only
DSM & Managing Director (MD)
DSM, MD, & Sales Director (SD)

There are three factors used to determine the required signatures.
So, there are three text boxes on my form. I have named those
controls as follows:
txtCM (Contribution Margin) [intCM]
txtRV (Revenue) [intRV]
txtCst (Cost) [intCST]

And here are the different scenarios:
1. If intCM > 30 and intRV > $5000 then DSM Only
2. If intCM > 30 and intRV < $5000 and intCST <$10000 then DSM Only
3. If intCM > 30 and intRV < $5000 and intCST >$10000 then DSM & MD

4. If intCM >0 but <30, intRV > $5000, and intCST < $5000 then DSM
Only
5. If intCM >0 but <30, intRV > $5000, and intCST > $5000 then DSM &
MD
6. If intCM >0 but <30, intRV < $5000, and intCST < $5000 then DSM
Only
7. If intCM >0 but <30, intRV < $5000, and intCST > $5000 then DSM &
MD

8. If intCM < 0 (negative margin) then DSM, MD, & SD

My idea was to create a command button that will create an index
number based on each scenario which will return the same value for the
signature(s) required. For instance:
Scenario 1, 2, 4, & 6 return one value which I can then assign as DSM
Only
Scenario 3, 5, & 7 return one value which I can then assign as DSM &
MD
and, of course #8 stands alone with another value for all three
signatures.

I've tried a variety of If..Then statements to assign different
indexes to each criteria such as:

If intCM > 30 then
intCMIndex=5 (as an example)
Elseif intCM < 0 then
intCMIndex=1
Else
intCMIndex=3
End if

and so on for the other two values and then a final index of:
intSigIndex = intCMIndex + intRVIndex + intCSTIndex

But I can figure out how to get them to add up to a single value to
represent each of the signature scenarios (perhaps this would be a
good question for my old Calculus professor...I didn't do so great in
that class). Plus I just think there's got to be a better way! I'm
thinking a series of CASE statements would work better, but I don't
have any experience working with those.

I apologive for the extensive post. Any help would be appreciated!