Coding Help Needed!!

[Guest]

Hello all,

I have a table "Application" where sometimes two agents share equally in a
insurance application. I need to divide percentage between them equally so
both agents get a fifty, fifty slice.

Right now I have a combo box that I select an agent name from. I can add one
or the other, but not both agents names without filling out a whole new
application record.

Here's what I would like to do:

1. Add first agent into the "Producer" field to create the first record.
2. Add second agent into the "Producer" field and make this a second.

I hope I explained this right, and I hope this can be achieved too!

Thanks in advance...

-Sky

 

[Guest]

I don't think there is any way of putting two seperate pieces of data into
one field. I'm still learning this myself, so please take this with a grain
of salt.

Why not create a second field for 'Producer2'. Then you could test for a nul
value when you go to figure out if you need to split commissions.

Alternately, maybe you could concatenate the string names into one string. I
don't know if this would work, only a guess, but then you would have to
seperate the string if possible, and then figure out your math for splitting.

Like I said, I'm no expert, just something to think about.

 

[Roland Alden]

You have a data model problem here. The problem is that there can be
multiple "producers" for a given "application" and your underlying data
model should reflect that.

Rather than have a "producer" field in the application you should have a
separate table that contains records that relate one or more producers to an
application. I.e.,

table application: ID, some more stuff
table producer ID, stuff about the producer
table proapp ID,ID

in proapp you create a new record holding a pair of ID (these are unique key
fields of course) that says "for this application this producer was
involved". In your application logic you can do a few different things. For
example, given a particular application it is easy to form a query to
determine how many producers were associated with that application. If the
answer is 2 maybe your commission split is 50%/50%. If 3 then 33.3% blah
blah. Or, you might want to add another field to proapp that holds the
percentage due this particular producer: i.e.,

table proapp ID,ID,myCut

You would want to bury pretty deep some logic to make sure the sum of myCut
for any given application is always 100%; you could do that in an Update
trigger.

For a user interface it would be easy enough to put a table view subform on
your application form that show the list of producers associated with this
particular application and allow them to be edited, etc.

 

[Guest]

Ronald,

I created a second table to hold just Producer names and Percentages which
solves my dilemna. Thanks for pointing me in the right direction, you saved
me loads of grief.

Thanks to Paul B too for your input

-Sky