Calculation depends on different values..

[pookie62]

Hi,
Best to have a look at attached relations picture.
Basicly this is what I'm wondering about:
I add a new customer who bought a product from a specific supplier.
That unique combination gives me the right on commission.
From company A I get, let's say 5% of the capital.
Company B gives me 4,25%

Products are insurance policies, so at certain products you are insured for a capital i.e €100.000,-
Percentages given are to be calculated of that capital.

So I have a lot of possibilities here which determines my provision.
What is the best way to handle this ????
I have no clue where to start.

The field [Polissen.Provisie] is now manually filed in on the Form where I add all details, which should remain possible (sometimes there are different agreements concerning the fee)
Hope it's a bit clear..
Please ask if more info is wanted.

 

[neileg]

I don't speak Dutch so I can't tell what your tables do.

But what I can say is that you need to map out exactly what the real world position is on your commission. You can't throw it into Access and hope that will sort it out for you. If you can't write it down on paper, you can't program it in Access.

 

[pookie62]

Not sure what you mean by that, but anyway..
I've been playing with Queries and I have working queries now for every 'fee construction'.
So, question that remains is how to implement those queries so that when I fill in the Form with details about customer, which product from what company, the field Fee is filled with the outcome of the query.

 

[neileg]

Not enough information. Can you post your database so we can have a look.

 

[GaryPanic]

Ok I am in insurance so kinda know what you are talking about

before we start on access lets get to basics
you have a product call A and when ever A is entered in your system you want its commission level also to appear -

(Hope I have this right)

ok you need a table with products in it
I make this up as i go along

Product A (car insurance)
supplier Cornhill (making it up as i go along)
Commission 5% might be better to do this as 0.05

so you now select product A it will fill in on your form
product
supplier ??
and CommissionRate 0.05 or 5% whatever

Product B holiday insurance
supplier SAGA
Commission 4.25

now tie this to a combo box and the after update properties of this
tie your combo box to a qry tied to the table with all the info in it
this will copy your table information in products onto your mainform/table you can then if required overtype the values
then you need to do an after update on your premium field that will work out
premium (I presume tax as well) your retained commission and any silent agent/introducing commission

 

[GaryPanic]

you can make your product table as complicated as required - as long as you use the afterupdate on a combo to fill in your fields

and remeber to put a refresh & save option in as well
otherwie when you change produt the changes will not follow through

 

[Ron_dK]

I don't speak Dutch so ..

Reading dutch would be sufficient Neil

I've been playing with Queries and I have working queries now for every 'fee construction'.
So, question that remains is how to implement those queries so that when I fill in the Form with details about customer, which product from what company, the field Fee is filled with the outcome of the query.

Why not post a copy of your form and the bound query and rephrase your question.

 

[neileg]

Reading dutch would be sufficient Neil

I'm waiting for my next lesson.

 

[pookie62]

Ok I am in insurance so kinda know what you are talking about

before we start on access lets get to basics
you have a product call A and when ever A is entered in your system you want its commission level also to appear -

(Hope I have this right)

ok you need a table with products in it
I make this up as i go along

Product A (car insurance)
supplier Cornhill (making it up as i go along)
Commission 5% might be better to do this as 0.05

so you now select product A it will fill in on your form
product
supplier ??
and CommissionRate 0.05 or 5% whatever

Product B holiday insurance
supplier SAGA
Commission 4.25

[edit]
Part 2 [/edit]

now tie this to a combo box and the after update properties of this
tie your combo box to a qry tied to the table with all the info in it
this will copy your table information in products onto your mainform/table you can then if required overtype the values
then you need to do an after update on your premium field that will work out
premium (I presume tax as well) your retained commission and any silent agent/introducing commission

Hi Gary,
You got the idea..
I added in tbl "Verzekering" a field ProvisiePerc with the %Rates appropriate for the product linked
I was playing with queries and through VBA selecting if v_Id (unique ID for combi Company/product) is 1 then run query A etc.
I have different queries working for the different v_id's.

That covers first part of your reply I think.

Second part:
In the Form I have Mainform is for custumordetails, subform is salesperson and product details. I already have a combobox for the Company/Product combi.
I don't understand clearly what you mean with the combobox for the Commision part.

I posted in the VBA Forum as well how to set the code for Case Select..

 

[GaryPanic]

have a look at the customer table enter in custer info
ignore SI
select a product from the combo drop down
enter in a price it will give you retained commission


does this do what you want

ignore both flash forms (someone else needed this)

 

[pookie62]

Oke, got some inspiration from this..
Thanks again, Gary !
If I can't get it done, I'll post again...

 

[GaryPanic]

if its too long post me direct and on the forum - as I am out and about tomorrow
g

 

[The_Doc_Man]

Pookie, here is something Neil passed along but I think it requires explanation.

"If you can't do it on paper, you can't do it in Access."

This is true but not obvious unless you understand what it REALLY means. It is a quick way of saying that until you can do everything by hand, Access should not enter into the picture. You must know the problem well enough to be able to fully perform every aspect manually. THEN you can begin to build an application to do the job by automation. The key is that until you understand EVERY STEP of the operation, you aren't ready. Not that you would have to be able to do it quickly by hand, you understand, but you must be able to do it by hand completely.

It's like the difference between a USA cowboy quick-draw artist vs. a military sniper. The quick-draw artist pulls the weapon, brings it roughly to bear, and probably "shoots from the hip" (the origin of that phrase in the USA). The sniper slowly studies his target, stalks it, understands its movements, and chooses the time when he knows everything he needs to do to make the shot. And the results of these two approaches are appropriately similar to Access results. Shooting from the hip means you have a good chance of missing the target. Taking the time to refine your aim massively improves the chance of hitting the target on the first try.

Access, being what is called a "rapid application development" tool, allows you to shoot from the hip. But it is an illusion of progress to do so. You have incredibly bad odds of hitting what you wanted on the first try when that happens. The longer you take to study your application target and learn about it, the better your chances of success.

 

[pookie62]

Hi Doc Man,
Well that certainly shines some light on this !!
Thanks for the explanation, and funny thing is, I practice fast shooting for real ! How about that !? LOL Not the fast-draw, but steelplates where the clock (and misses) are your enemy..
Anyway, I think you're right, but still, without trying and finding on the way that you get stuck or need to re-design is also a way. Depending who you're designing for, the consequences are with or without problems.
In this case it's pure to make my wife's life easier, a nice looking report is so much better than scrolling down a few hundred lines in Excel until you see what you want.

Cheers,
Hans

 

[GaryPanic]

Doc man

I kind a always go down this route --normally I would recomend a spreadsheet approach
field 1 what is it and what do you want to do with it = name of customer - right its a text field
field 2 is a value - right what value financial or unit field currency or number etc
that way you can say field 10 is the product of field 8 + 9
writing this out rather longer than you need -acutal makes the person question some of there practices and also gives some insight into possible alternative solutions nearly everyone can make Access add and multiply etc
which is what most people want really

its the data interigation that becomes complicated the table layout should be relative simple -

Get the table right -and the rest really should be plain sailing (relatively ) and there is always an exception to a rule

understanding tables that need to join and how they are joined once written suddenly becomes easier to do when you see it on paper - rather than in you own mind

you can then do fault tree with senarios
if this then field = A if field = A then do soemthing else
- if that then field = B ifield=B do soemthing else or do nothing

start projects

with mind maps

what is the centre of this projects world is it customer or a product

draw it and start linking it to the other tables

lots of products then the customer might be the centre of prtojects universe

 

[The_Doc_Man]

Gary, if it works for you and the result is good, I'm enough of a pragmatist to say "good work." Any tool that brings you closer to your goal is a good tool.

My own favorite advice is the dry-erase board & markers, post-it notes, and lots of other paper on which to take copious notes about the diagrams you will generate. If you REALLY get it right, a digital camera isn't a bad idea either. Hell, organize by dropping pebbles in tin-cans if that paradigm makes sense for your problem.

The whole point is - design first, implement second, through any means that float your boats. Design time spent BEFORE implementation is always time well spent.

 

[GaryPanic]

my spreadsheet option was after my mind maps which was after bits of paper - which were after more bits of paper etc.... which started out as what am I trying to do where do i start

what is the most important thing
what is the hardest thing


Mine is acutal a range of product (insurance policies)
the insured (account) and the market - provider where easy its was a mulitple product table with variables in it

I am about to re-write the whole thing from the begining again - just to tidey up what i have done and also reviewing what others have done as well

the hardest part was a letter writer option - however from what I have heard Access 07 solves this

which will be brill with its richtext option in memo fields