Don't know where to start

[Ember]

I am trying to create a database which will track the invoicing of insurance
policy premiums. The "parent company" "holds" the policy for which multiple
"managed" companies are listed. The parent company then invoices each
managed company for its portion of the policy premium.

Here's where it gets fun...depending on the purpose of each managed company
the formula for calculating its portion varies. Some parts of these formulas
are set values that change each year, while others are calculations based on
data that is stored in other tables in the database.

Additionally, I have now been asked to keep a history of the outputs of each
invoicing cycle (for future comparisons). So I am scratching my brain trying
to figure out how to create a table/query/form/report structure that will
accomplish what needs to be done.

I am not sure what more information any of you will need to assist, so I
will leave my post as is and respond as questions come in.

Thanks!
Ember

 

[John W. Vinson]

I am trying to create a database which will track the invoicing of insurance
policy premiums. The "parent company" "holds" the policy for which multiple
"managed" companies are listed. The parent company then invoices each
managed company for its portion of the policy premium.

Here's where it gets fun...depending on the purpose of each managed company
the formula for calculating its portion varies. Some parts of these formulas
are set values that change each year, while others are calculations based on
data that is stored in other tables in the database.

Additionally, I have now been asked to keep a history of the outputs of each
invoicing cycle (for future comparisons). So I am scratching my brain trying
to figure out how to create a table/query/form/report structure that will
accomplish what needs to be done.

I am not sure what more information any of you will need to assist, so I
will leave my post as is and respond as questions come in.

Thanks!
Ember

Without knowing anything about the nature of the calculations, it's hard to
say. If the "shape" of the expression is the same for each company, just with
different numeric values, you should be able to put the numeric parameters in
a company table, or a company-year table related one to many to the company
table; but if the algorithm is different, it may be a lot more complex. You
could store an algebraic expression as a text string in a table field and
evaluate it, but it won't be a trivial exercise!

 

[Ember]

Okay, for the post below I think I have figured out how to start but not sure
who to make it work.

I have a table (tblAllocations), currently this table has the following
fields (more to come):
Allocation_ID
ActPropPre
PropMultiplier
ChgPropPre

I have a query (qryAllocations) that has all the above fields except
"ChgPropPre". In its place I have an expression "ChargedPremium:
[ActPropPre]*[PropMultiplier]".

I have a form (frmAllocations - see a theme ) that uses the query as its
data source. From the form the user would input the ActPropPre and the
PropMultiplier and the query would populate the ChgPropPre field (using the
expresion from the query).

Now...the table (tblAllocations) is updated with the entered ActPropPre and
PropMultiplier from the form, however the ChgPropPre field is blank.

I tried to create an Apend Query (linked on the Allocation_ID field) that
would append tblAllocations with the value from the expresion the first query
created, however this just added records and did not append them to the
associated ActPropPre and PropMultiplier entry.

What resulted was that my tblAllocations had five records that held the data
that was entered from the form and five additonal records that just held the
data from the expression. Something like this:

Allocation_ID ActPropPre PropMultiplier ChgPropPre
1 $1,000 1.30
$0.00
2 $1,000 1.30
$0.00
3 $1,000 1.30
$0.00
4 $1,000 1.30
$0.00
5 $1,000 1.30
$0.00
6 $0.00 0.00
$1300.00
7 $0.00 0.00
$1300.00
8 $0.00 0.00
$1300.00
9 $0.00 0.00
$1300.00
10 $0.00 0.00
$1300.00

Don't know if this makes any sense, but I would appreciate any help anyone
could give.

Thanks!
Ember