Allocation of compensation by month
am 01.02.2008 13:49:43 von PaulRed
Help! I'm a relative novice (compared to all of you!) re Macros and VBA.
I'm trying to track commissions earned from the sale of insurance products.
When someone pays an annual premium, my commission is earned immediately and
can be allocated to a particular month. But when someone pays their premium
monthly, I'm paid 1/12 of the commission over the next 12 months.
I'm trying to run a crosstab query that will reflect 1/12 of the income in
each month.
I suppose I can create a routine in VBA that that will add 12 new records to
the Compensation table reflecting the different months and the amount for
each month.
Is there a better way?? Is there a way without adding 12 new records where
the system will interpret "M" for monthly for "Q" for quarterly and will add
the appropriate amounts in the appropiate months?
I guess the easiest way (maybe the only way) is to have a separate line entry
for the month and the amount in order to have it reflect in the crosstab
query.
Thanks for your help. {This forum has been phenomenal for obtaining answers
to my problems!)
--Paul
Re: Allocation of compensation by month
am 01.02.2008 14:59:33 von Salad
PaulRed wrote:
> Help! I'm a relative novice (compared to all of you!) re Macros and VBA.
> I'm trying to track commissions earned from the sale of insurance products.
> When someone pays an annual premium, my commission is earned immediately and
> can be allocated to a particular month. But when someone pays their premium
> monthly, I'm paid 1/12 of the commission over the next 12 months.
> I'm trying to run a crosstab query that will reflect 1/12 of the income in
> each month.
> I suppose I can create a routine in VBA that that will add 12 new records to
> the Compensation table reflecting the different months and the amount for
> each month.
> Is there a better way?? Is there a way without adding 12 new records where
> the system will interpret "M" for monthly for "Q" for quarterly and will add
> the appropriate amounts in the appropiate months?
> I guess the easiest way (maybe the only way) is to have a separate line entry
> for the month and the amount in order to have it reflect in the crosstab
> query.
> Thanks for your help. {This forum has been phenomenal for obtaining answers
> to my problems!)
> --Paul
>
I don't know if this will work but it should. You might need to be able
to create write VBA code to make it work.
Create a crosstab query for the records but exclude the "M" records.
Run it and look at the output. Count the columns you have, note the
headings.
Now create another query for the "M"s. Select your basic fields then
create columns for 12 months of money data or whatever is in the 12
columns. If it's simply a matter of dividing the value of the
commission in the query builder enter something like
Month1 : ([CommissionAmount]/12)
.
.
,
Month12 : ([[CommissionAmount]/12)
If you don't have the commission amount available then write a function
Month1 : CalcMoney([ID]) ...etc for the next 11 months
In your function, from the ID, get the amount and calculate the commission.
Function CalcMoney(lngID As Long) As Currency
'create code to get amount and calc
End Function
Now you have 2 queries. Lets say they are Query1 and Query2. Now
simply do a
Select Query1.* From Query1
UNION ALL
Select Query2.* From Query2
The column counts must match in both queries.
Lunatic Fringe
http://www.youtube.com/watch?v=TksAGzfw8Z0
Re: Allocation of compensation by month
am 01.02.2008 15:04:58 von Allen Browne
There are always 2 separate issues to consider when designing a database:
- how to store the data in a normalized (relational) format.
- how to interface the data in a meaningful (human) way.
For storage, you might consider fields like this:
CommissionAmount Currency
CommissionFreq Number (Long integer)
CommissionPeriod Text (4 characters) - combo box
CommissionCount Number (Long integer)
CommissionStart Date/Time (date of first payment.
So, if the commission is $50 per month for the next 12 months, starting
1/1/2008, the record would contain:
CommissionAmount $50
CommissionFreq 1
CommissionPeriod m
CommissionCount 12
CommissionStart 1/1/2008
(This assumes that the amounts will always be exactly the same, and evenly
spaced, until paid out.)
The combo contains the choices:
"d";"m","q";"yyyy"
These are exactly what is needed in a DateAdd() expression, so you can add
calculate the dates when commission is to be paid.
Now you need another table with the values 0 to 1 less than the highest
CommissionCount value you could ever need (11 for 12 months, or whatever.)
This table needs only one field named (say) CountID, type Number (Long),
marked as primary key. Save the table as (say) tblCount, and enter the dozen
records (starting from zero.)
Now create a query that uses both tables, with *no* line joining them in the
upper pane of table design. This gives you every possible combination of the
2 tables, i.e. multiple copies of the records in your main table.
In the Criteria row under the CountID field, enter:
< [CommissionCount]
This restricts the query to the right number of commission payments.
Now enter this expression in a fresh column in the Field row:
PayDate: DateAdd([CommissionPeriod], [CountID], [CommissionStart])
This column will now contain the 12 monthly payment dates, for the example
above.
Since you now have a record for each payment date, you can go ahead and
create your crosstab query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"PaulRed" wrote in message news:7f14b1c63f9de@uwe...
> Help! I'm a relative novice (compared to all of you!) re Macros and VBA.
> I'm trying to track commissions earned from the sale of insurance
> products.
> When someone pays an annual premium, my commission is earned immediately
> and
> can be allocated to a particular month. But when someone pays their
> premium
> monthly, I'm paid 1/12 of the commission over the next 12 months.
> I'm trying to run a crosstab query that will reflect 1/12 of the income in
> each month.
> I suppose I can create a routine in VBA that that will add 12 new records
> to
> the Compensation table reflecting the different months and the amount for
> each month.
> Is there a better way?? Is there a way without adding 12 new records where
> the system will interpret "M" for monthly for "Q" for quarterly and will
> add
> the appropriate amounts in the appropiate months?
> I guess the easiest way (maybe the only way) is to have a separate line
> entry
> for the month and the amount in order to have it reflect in the crosstab
> query.
> Thanks for your help. {This forum has been phenomenal for obtaining
> answers
> to my problems!)
> --Paul