Create an index based on multiple criteria

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!

0
kidkosmo2
9/7/2007 1:07:39 AM
access 16762 articles. 3 followers. Follow

2 Replies
734 Views

Similar Articles

[PageSpeed] 6

On Sep 6, 7:07 pm, kidkos...@yahoo.com wrote:
> 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....

0
kidkosmo
9/7/2007 3:43:54 AM
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


<kidkosmo2@yahoo.com> wrote in message
news:1189127259.055623.285990@y42g2000hsy.googlegroups.com...
> 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!
>


0
tina
9/7/2007 3:47:07 AM
Reply:

Similar Artilces:

word cannot save or create this file
i have opened a saved doc. When i re-open and edit and then "save as" new name an error: word cannot save or create this file. Check the disk is not full or write protected. This is new, how do i save? -- Browny ...

How to catch a WM_COPY message in a CRichEditCtrl based object
I have a CRichEditCtrl based class, and I want to catch the WM_COPY message. I used the ON_MESSAGE(WM_COPY, OnCopy) macro in the MESSAGE_MAP, and then defined a LRESULT OnCopy(WPARAM, LPARAM); function. However I am not getting a breakpoint in that routine. Do I need to be using Reflection? Any ideas on how I can catch that message so I can do special processing? Thanks! Under what conditions are you not catching WM_COPY? Ctrl+C? It probably doesn't send a WM_COPY message (Microsoft is not very good about doing things the right way, just doing things the clever way) joe On 3 Ja...

creating a macro
I am creating a sheet that has 3 basic columns. (see below). What I want is to create a macro that will automatically give me the lowest bid of the three. I would also like to create a column that shows the differnces in the three. For example Estimate #1 is $500 more than estimate #2; Estimate #3 is $700 lower than Estimate #1 and $200 lower than Estimate #2 Can this be done? A B C D ESTIMATE #1 ESTIMATE #2 ESTIMATE #3 BID $2,000.00 $1,500.00 $1,300.00 Any formula for th...

Run a query based on user input to a text box
I am creating a database to manage Auction Bids for a live radio station phone auction weekend. I have a form where the user is entering names as the bidders call in. I want to be able to have entry into a text box cause a query to run and populate a list box so that if a caller has been entered before, the user can click on an existing name. So for example, if the first keystroke is A , then the list box will be populated with all names beginning with A. If the next keystroke is l to give Al then the list box will be repopulated with only names beginning with Al (e.g. Alice, Aliso...

Create datasheet in MDE
My application creates a datasheet on the fly. Users make a selection from a set parameters including column names, sort order, etc and the code populates a querydef with the set of columns selected by the user. This works fine in an MDB. The equivalent MDE crashes - "That command isn't available in an MDE database" Please tell me how to add/delete columns from a querydef in an MDE file? DT On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" <dreddog69@hotmail.com> wrote: What command? Surely MDE would allow you to create a sql statement and ass...

How to set up multiple users with same email account
I currently have the following set up on my XP Home SP3 using Outlook Express 2 user accounts on XP (mine and my wife's) 3 email accounts - all from one ISP (pop3) - (one for me only, one for my wife only, one for both of us) The address book is shared out to all email accounts. As Outlook Express is not supported in Windows 7 and I am not too keen on Windows Live Mail, I was thinking of moving over to Outlook 2003 (which I also have). Is it possible to set up the same construct I have with OE using Outlook 2003 and how is such a thing possible. I have seen articles ...

Combo Box Value that acts like multiple values
Hi there, I have a combo box [Combo30] on a form that triggers an event OnChange. The drop down lists different Party Rooms that we have at our facility. When one of the Rooms is selected, it runs a report based on a query that lists all upcoming parties in the room that is selected. In the query, the Party Room we selected is called: [Forms]![Party Transaction Selector]![Combo30] The Party Room names are stored as a value list. My question is, can we create a value that selects two rooms? That is, rather than running the report for "Red" and then "Blue", can we make on...

multiple payable/purchase daybook
In Belgium we are used to work with daybooks. From the payables/purchase side it is not possible to have more than 1 daybook, because there is only one voucher number series. Anyone knows if there are add ons for this? Other suggestions? Hi pvbe, Thanks for your question. Are you talking about VAT daybook functionality in Belgium? If so, I will continue to offer assistance with setup. Thanks, Jonny --- This posting is provided "AS IS" with no warranties, and confers no rights. "pvbe" wrote: > In Belgium we are used to work with daybooks. > From the payabl...

Using excel as a data base
How to create names and addresses in EXCEL that can be used in a mail merge in Word One row per record. Each field in a separate cell/column. It's much easier to combine fields than it is to separate them correctly. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. Nan wrote: > > How to create names and addresses in EXCEL that can be used in a mail merge > in Word -- Dave Peterson ...

Workflow to perform actions when task created not working
I have some workflows that create task activity assignments for new clients (Accounts). the workflow rule is 'manual' and works just fine. The issue is that I had to create an additional workflow rule for 'task' entity, so that when a task is created, the system can check the subject, and then update a custom picklist field with a certain value from the list. The 'manual' workflow creates the task activities, but the 'create' workflow rule for tasks is not even launching. No action takes place. Does anyone have an idea of what may be happening. I am...

Countif accross multiple questions.
Hi folks, I know this has been asked and answered, but I could not get it working. I thought I would ask my specific problem. I have a excel spreadsheet with a detail sheet containing columns. One column is Inventoried whose values are TRUE or FALSE. Another column is PRICE which could be 0 or any number like 5.35. In anoher sheet I want a summary. I have one cell that is inventoried (=COUNTIF('INVENTORY'!E:E,"TRUE" I need another Cell that will be Unpriced inventory which is the number if Inventoried = 'TRUE' and price = 0. I tried the sumproduct but keep get...

criteria failure
i have a query consisting of the following fields: description, date and amount. i have created two more fields-expressions. i.e. Expr1=Month([date]) and Expr2=Year([date]). i have a main form which consists of three combos that serve as criteria selection for my query: combo1=description selection, combo2=month selection and combo3=year selection. i have entered the following criteria in my query: description field=iif ([forms]![main form]![combo1] is null;[description];[forms]![main form]![combo1]) which works fine, that is if the combo has no data the query returns all the records an...

How create ranking within subgroup
Hi all, I have a SQL question. I have a table which contain fields: Product, Customer and SpendByCustomer What I would like is to create a query of the top spend by customers (aggregated) by Product. I would like the query list by Product, then by the top spend customer (in descending order) and a new field that would tag it as such. For example: Product Customer Spend Order 1 ABC 1000 5 1 CDE 500 4 1 FGH 400 3 1 IJK 300 2 1 LMN 100 1 2 FGH 400 3 2 CDE 100 2 2 ABC 50 1 The value of the Order field is based on number of customers for t...

CDialog::Create() is blocked in GUI thread. Why?
Hi all, I am trying to use another GUI thread to show a dialog, but I found the CDialog::Create() will be blocked in the GUI thread. I start the GUI thread from a function in my main thread by using a calss that is derived from CWinThread. In CWinThread::InitInstance(), I new a dialog object and call its Create(). I found the Create() will be blocked until the function in my main thread is leaved. Have you encountered such situation before? Any suggestion is welcome. Thanks in advance. Justi Lin Justin wrote: > > Hi all, > > I am trying to use anot...

Unable to create custom keyboard shortcut
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I get to where you are able to create a custom keyboard shortcut. But when I select my command &quot;NextChangeorComment&quot; and select the &quot;Press new keyboard shortcut&quot; field, nothing happens. No text shows up in the field, nor does the &quot;Assign&quot; button become selectable. Since you don't specifically say that you did so, once you click in the field you need to press the actual key combination you want to assign to the command. If you did so but nothing displayed in the field it&#...

Is it possible to create a product in MS CRM through CRM webservic
Is it possible to create a product in MS CRM through CRM webservices ...

OWA and multiple Exchange Servers
Hi, I'll first sketch my situation: 3 Exchange-servers (exch1,2 and 3), all in the same domain but on differtent locations. Local Domain Name is clientname.local. On one of the servers OWA is configured. Problem: I want all users to use exch1 as their OWA server, regardless of on wich server they actually have their mailbox. It works fine now for users on exch1.clientname.local (wich also has an external DSN entry on the internet (https://companyname.nl/exchange)), but as soon as users that exist on any of the other servers try to log in they get redirect to eg. http://exch2.clientname....

Virtual user supports in exchange server (a email account without actually creating a email account)
I want to provide email service to my customer, but I don't want to create real user account and email account for each one. for example, I want to assign email like xyz123@mycompany.com to my customer, but I don't really have a email account named "xyz123" setup for it. Still, I want to be able to look at all the new email in the exchange server and extract those emails and doing somework accordingly. Does anyone know if Exchange server support this feature? If not, any other window based email server support it? Thank you so much for the help.. --Xin Chen In news:...

Multiple logins
Here is the scenario: My Fiancee & I share some of the same accounts. When she logs into the bank, she can see our joint accounts plus her personal accounts. The same for me. The issue is when I set Money up to pull from the bank, I get two of the same account. If I try to delete one of the duplicates, it tells me it has to delete all accounts from that login (including the history). I do not want to do this, but what can I do to save my sanity? Thanks! ...

Role-based authentication from active directory
Hello, I want certain pages of my website visible only to a particular role in the network (Active Directory) How do I check for authentication from active directory (role-based) Eg-- I want the only managers in the active directory to access a particular page. Please provide me some code by which I can achieve this. Thanks On Feb 15, 8:31=A0pm, "Biranchi Narayan Panda" <biranchi_pa...@hotmail.com> wrote: > Hello, > > I want certain pages of my website visible only to a particular role in t= he > network (Active Directory) > > H...

How to create and access (add/delete mails) .PST file at runtime?
Hi, I need to store selected mails into some different .pst file at runtime. Also want to access those mails in .pst at runtime. I can import and export mails into .MSG file. But it is hard to maintain mails as .msg files. My queries are, 1. How to create/open .PST file at runtime ? 2. How to add/delete (import/export) mails (any format like .MSG, ..EML, etc.,) into .PST at runtime ? Is there any way to handle this kind of situation thru Outlook object model or CDO or Extended MAPI or Redemption or any other way? Thanks in advance with best regards, Kumar 1. Use Namespace.AddStore/Re...

syncing multiple handhelds to the same profile
I am trying to sync two handhelds to outlook for two different people and can't make it work. You know, we could spend a few mails back and forth asking questions that you should have included in your original post (version of Outlook, PDA brand, version of Activesync, Pocket PC or Palm OS version, and Chapura Intellimirror) but it is a nice day outside so I think I will go play instead. I will be a lot happier. You can repost this question in the microsoft.public.pocketpc.activesync (if it is a PPC) and include the above information. Folks there are less tolerant than we are here i...

Multiple versions on a single machine
I'm trying to install GP 10.0 without upgrading the current version 8.0 installed on my machine as I would like to use both (pointing to different databases). The problem is that GP 10.0 automatically tries to upgrade the previous version. Is there a way around this? Thanks! Craig Yes, create a new instance of SQL Server on your machine and a new DSN pointing to that instance. I covered this in my previous MSDynamicsWorld article at http://msdynamicsworld.com/story/configuration-development/set-microsoft-dynamics-gp-test-and-production-environments-budget Best regards, -- MG.- Ma...

Complex problem on easily creating and editing subsets...
Hey guys, I've got a bit of a complex problem. I have a spreadsheet that lists yearly sales information for all 50 states in the US. Then I have the last 3 and 5 years of total sales for each state. What I want to be able to do is create territories of these states and then be able to get the total 3 and 5 year sales for all the states in the defined territories. This can be done normally just by adding things manually but I'm wondering if there�s a way, perhaps with a macro, to define these territories and get the sums. The other problem is I also need to be able to easily modify th...

Typing in multiple row in a single cell
Is there a way to divide a cell in multiple rows? I am not talking about the insert the Text Box or table. The cell it self should be able to take text in multiple rows. for example to type "My address My Street My City" Three rows in one cell Khusro Hi Format>Cells>Alignment>Wrap text Then press Alt+Enter at each point you want to create a new row. As an aside, entering data in this format is not the most useful way to store data. Very often, you want to pull out the City separate form other things, for example. It would be better to store your data in separate ce...