Saving Calculated Data

[DCH]

Storing Calculated Data (I can’t think of another way)

I am creating a database to track residual monthly commission payments over
the life of a contract. Contracts can be will have addendums each month with
+/- $ affects on the duration of the contracts commission payoffs.
I need a way to keep record of the original contract payouts, as well as the
modified payouts, and the only way I think to do it is having a Commission
detail table, w/ records of each month’s payoff. Then I can modify the
remaining payments post add/subdendum.
Has anyone any experience with residual contract commissions and Access who
might help with suggestions.
Many Thanks, David.

 

[John W. Vinson]

Storing Calculated Data (I can’t think of another way)

I am creating a database to track residual monthly commission payments over
the life of a contract. Contracts can be will have addendums each month with
+/- $ affects on the duration of the contracts commission payoffs.
I need a way to keep record of the original contract payouts, as well as the
modified payouts, and the only way I think to do it is having a Commission
detail table, w/ records of each month’s payoff. Then I can modify the
remaining payments post add/subdendum.
Has anyone any experience with residual contract commissions and Access who
might help with suggestions.
Many Thanks, David.

Why does this require storing calculated data?

You can use a Totals query to sum all the debits and credits.

If the calculations are *demonstrably* too inefficient you can store the
totals, but you'll need to do so with great care to avoid having an incorrect
sum stored in the table.

John W. Vinson [MVP]

 

[DCH]

Thanks for the contact John.

Please note I am very new to Access, and could be looking at my issue
entirely incorrectly. Let me explain the trickier parts of the Form I have
created called CONTRACT ENTRY FORM. My fields are either: (S) Stored in a
Table, or (C) Calculated.

1. (S) Contract Begin Date
2. (S) Terms (12, 24 or 36 Month)
3. (C) Contract End Date
4. (S) Contract Non Recurring Costs
5. (S) Contract Monthly Recurring Costs
6. (S) Bonus Status (calculation based on Rep their sales performance for
the month)
7. (C) Recurring Monthly Payment to Rep (8.5% or 10% of Contract Monthly
Recurring Costs dependant on Bonus Status.)
8. (C) 1st Month Payment to Rep (Recurring Monthly Payment + 6% Non
Recurring Cost)
9. (C) Payment to Rep Date (12, 24 or 36 fields calculated from Terms and
Contract Begin Date)
10. (C) Payment to Rep (12, 24, or 36 fields with dollar amounts
corresponding #9 above)

My intent was to save all of the calculated data into a new table (even new
database) and make any modifications to the monthly payments to Reps (both
Non Recurring and Monthly Recurring) there. I was hoping to prevent any
modification to previous CONTRACT ENTRY FORM records, and carryout all
modifications on the new table/data base.

You’re a truly a hero if both understand my issue, and are willing to help
out.
Thank you, David.

 

[John W. Vinson]

Thanks for the contact John.

Please note I am very new to Access, and could be looking at my issue
entirely incorrectly. Let me explain the trickier parts of the Form I have
created called CONTRACT ENTRY FORM. My fields are either: (S) Stored in a
Table, or (C) Calculated.

Comments inline.

1. (S) Contract Begin Date
2. (S) Terms (12, 24 or 36 Month)
3. (C) Contract End Date

This can be calculated whenever it's needed - DateAdd("m", [Terms], [Contract
Begin Date]) - on a Form, Report, in a query, wherever.

4. (S) Contract Non Recurring Costs
5. (S) Contract Monthly Recurring Costs
6. (S) Bonus Status (calculation based on Rep their sales performance for
the month)
7. (C) Recurring Monthly Payment to Rep (8.5% or 10% of Contract Monthly
Recurring Costs dependant on Bonus Status.)
8. (C) 1st Month Payment to Rep (Recurring Monthly Payment + 6% Non
Recurring Cost)
9. (C) Payment to Rep Date (12, 24 or 36 fields calculated from Terms and
Contract Begin Date)
10. (C) Payment to Rep (12, 24, or 36 fields with dollar amounts
corresponding #9 above)

All the payments should be stored AS INDIVIDUAL RECORDS in a Payments table
related one to many to the Contracts table. They should certainly *NOT* be
stored in multiple fields in one table!

"Fields are expensive, records are cheap". You're using a relational database;
if you use it relationally - with one-to-many relationships between tables -
rather than as a spreadsheet, you'll find that it works much much better.

My intent was to save all of the calculated data into a new table (even new
database) and make any modifications to the monthly payments to Reps (both
Non Recurring and Monthly Recurring) there. I was hoping to prevent any
modification to previous CONTRACT ENTRY FORM records, and carryout all
modifications on the new table/data base.

If each payment (prospective or actual) is stored as a separate record in a
table, then no modification to the Contract table will be needed whatsoever.

You’re a truly a hero if both understand my issue, and are willing to help
out.

I hope I've at least pointed you in a direction that will help!

John W. Vinson [MVP]

 

[DCH]

John,

Again thank you for commenting. I understand your suggestions, however have
the following concerns. If I don’t have Access calculate the individual pay
dates and payoffs, I open the database to a lot of user error, not to mention
a lot of time spent on data entry. The user will need to enter up to 36
different dates and 36 different payments.
I spent the majority of the night redoing the data base, which now provides
a neat and accurate report on all of my sample contract detail, including
calculated payment dates (1 object) and calculated payments (1 object).

Can I make another report which searches for all dates in this created
report detail (again they are calculated) which match a particular month? If
so how can I get access to understand that the object next to it is also
relevant (the payout).

The end game is a report expressing how much I pay the reps each month.

Many thanks,
David.

 

[John W. Vinson]

John,

Again thank you for commenting. I understand your suggestions, however have
the following concerns. If I don’t have Access calculate the individual pay
dates and payoffs, I open the database to a lot of user error, not to mention
a lot of time spent on data entry. The user will need to enter up to 36
different dates and 36 different payments.

It's not necessary for the user to enter the data. You can certainly run an
Append query from code, adding 36 records. It would be no harder than storing
36 calculated fields - probably easier in fact.

I spent the majority of the night redoing the data base, which now provides
a neat and accurate report on all of my sample contract detail, including
calculated payment dates (1 object) and calculated payments (1 object).

Can I make another report which searches for all dates in this created
report detail (again they are calculated) which match a particular month? If
so how can I get access to understand that the object next to it is also
relevant (the payout).

Of course. Bear in mind that you have not posted the structure of the table(s)
so I can't say exactly *how*, but with properly normalized tables this should
be straightforward. If you'll post your table structures and the business
rules which determine payment dates and payments, I may be able to suggest an
append query to populate the table.

The end game is a report expressing how much I pay the reps each month.

Just don't confuse data *presentation* (your report) with data *storage* - nor
should you let the desired data presentation format control your table
structure. It's essentially always better to store the data in properly
normalized tables and create a (possibly somewhat more complex!) Query and
Report to massage it into the desired presentation.

John W. Vinson [MVP]