Formula imput - which way is more effective (1 Viewer)

[Bobert]

I am looking to do a calculation that in excel would be a snap, but im looking to do it in the access database for the functionality that comes with it. I have a bunch of Agent codes and with each is associated a certain percentage split between them. So for example: 123 = 40% agent A and 60% agent B. These codes are listed in a dropdown menu so upon selection i would hope the formula would apply. There are maybe 15-20 of these codes, and they range from 1-3 agents. Would it be easiset to do a ton of If statements in an unbound text box? OR would it be easier to make a calculate button with If statements to calculate upon click. A brief exmple either way (or any orther ways that are better yet) would be greatly appreciated.

Once again what im looking for (abridged version):

1. pick the code
2. Input doller amount
3. Have it autocalc (if possible) or just calc on click

Also to be noted: the percentages are being taken of of a manually input percentage depending on other factors. So in essence the fields to be worried about are Initial doller amount, percent of that, and then the percent split off of that number. Thanks in advance!


-Bob

 

[mhartman]

Hello:

What you could do is use the SELECT CASE statement to solve this problem: For instance,

Select Case AgentCode
Case 123
Run code here
Case 456
Run code here
Case 789
Run code here
End Select

Regards
Mark

 

[Pat Hartman]

I'm going to guess that your table looks like your spreadsheet. You have a repeating group - agentA, agentB, ... Each agent, along with his split, belongs in a separate row.

 

[Bobert]

Ok, I am very sketchy on actual coding. Ill try and clarify what I mean though, i dont know if i explained properly.

I have 3 text boxes for display of commission splits for 3 agents "A", "B", "C".

There is a text box with a list of the split percentage codes ex. "123"

There is a textbox (maybe it should be a number field?) for input of the split percentage off the initial investment amount.

Here is an example of how it should work therefore...

Investment: $1,000,000
Sales Charge: 3.5% (35000)
Split Code: 123
We will say that 123 is representative of "A" getting 60%, "B" getting 30%, and "C" getting 10%

Finally the agent commission boxes will then (maybe onclick of a cmdbutton) display:

A: $21000
B: $10500
C: $3500

Say "123" was changed to "234" and then the splits were different, it would calculate the split percentage based upon the "234" code instead.

Thanks guys

-Bob

 

[The_Doc_Man]

Your data set is rather convoluted here because you are still thinking a la' spreadsheets.

How many agents are we talking about, total? Just a three-person office? Or can there be dozens of A's, B's, and C's?

To do this via Access and still be properly normalized, you would need a table that looks like this:

tblSplits
SplitCode, (some integer format)
AgentID, (some format)
Split, (some floating-point format)

Now, for each split code, you make as many entries as you need per split code, from 1 to a bazillion. For your 123 case, you would have 3 entries:

123, A, 60%
123, B, 30%
123, C, 10%

For other cases, make more entries.

Then you would need a list of items that included

tblCommissionables
ItemID
SplitCode
TotalValue
etc.

Then a table of agents who participated in the item

tblParticipants
ItemID
AgentID

Now, through a complex of JOIN queries (multi-layered), you can reach a situation where in a single query you can see the commissions for each agent for each item.

I would join the commissionable item to the participating agents as the first layer. Then I would join the split codes to the resultant query as the second layer. The 2nd query works because the first query would give you a list of participating agents and the split code for each item. You could compute each agent's percent of the item's total value in the 2nd query.

After that, you can do summations in any order to show whatever totals, breaks, etc. that you need to see.

 

[Bobert]

Your data set is rather convoluted here because you are still thinking a la' spreadsheets.

Oh how true that is. Yes, there will only be 3 agents. we have many more, however our team is only tracking 3 of them. If another was to take split in a case then we would just manually put in the numbres, but picking a splitcode and having an investment number make it easy.

I was thinking that this was as easy as having an unbound textbox just dependant on the seperate boxes to display the correct answer, but that doesnt seem like its the case afterall, at least from what ive been reading.

I miss old VB.

One last thing, are there any good self starter books for learning VB.net because if i keep up at this database design (sorta) stuff im goign to need to know it.

Is there any possible way to do that? or is it just simply impossible and I need to go any of these above said ways? I guess maybe im just giving myself false hope of an easy route.

-Bob