append multiple data every end of the month...for accrual pruposes

[ai_enjoi via AccessMonster.com]

i have a table tblPayee/Payor...with fields PID, Rate, Name...there are about
200 PIDs, with their corrseponding rates...these rates are subject to change..
.. what i want to do is that every last day of the month, i would like to
compute for the individual accrued balances ....id lyk to ask for possible
ways on how to do this...
i planned of appending multiple data on a table named tblFundAccruals w/
field names IndexNum, PID, Month and Rate whch i could not figure out how
because of the large no. of PIDs...
any help wud be appreciated ...thank u...

 

[Michel Walsh]

A possible solution is to build a table where rates are defined through
their valid historic range of dates:

Taxes ' table name

TaxRate, FromDate, ToDate ' fields

0.05 2000.01.01 2003.06.01
0.045 2003.06.01 2007.01.01
0.04 2007.01.01 null


and then, to get the applicable tax rate, check the date of the 'bill'



SELECT ..., taxes.taxRate, ....
FROM myTable INNER JOIN taxes
ON (myTable.DateOfBIlling >= taxes.FromDate AND myTable.DateOfBilling
< Nz(Taxes.ToDate, Date() ) )



Note that you have to check that the dates ranges are not overlapping,
without holes, when your end user modify it. The NULL value, under ToDate,
is intended to mean "up to unknown", so, we change that NULL by Date(),
today, in the query.



So, proceeding like this, you don't need to pump the data in 'historic'
table, just to get the "then applicable rate", since you got all the
required info "IN" your database.



Hoping it may help,
Vanderghast, Access MVP

 

[ai_enjoi via AccessMonster.com]

sorry if i did not make any clarifications on the rate..its supposed to be
due per month...its not actually tax rate... anywei, thank you for the
solution..ill be needing that...

A possible solution is to build a table where rates are defined through
their valid historic range of dates:

Taxes ' table name

TaxRate, FromDate, ToDate ' fields

0.05 2000.01.01 2003.06.01
0.045 2003.06.01 2007.01.01
0.04 2007.01.01 null

and then, to get the applicable tax rate, check the date of the 'bill'

SELECT ..., taxes.taxRate, ....
FROM myTable INNER JOIN taxes
ON (myTable.DateOfBIlling >= taxes.FromDate AND myTable.DateOfBilling
< Nz(Taxes.ToDate, Date() ) )

Note that you have to check that the dates ranges are not overlapping,
without holes, when your end user modify it. The NULL value, under ToDate,
is intended to mean "up to unknown", so, we change that NULL by Date(),
today, in the query.

So, proceeding like this, you don't need to pump the data in 'historic'
table, just to get the "then applicable rate", since you got all the
required info "IN" your database.

Hoping it may help,
Vanderghast, Access MVP

i have a table tblPayee/Payor...with fields PID, Rate, Name...there are
about

[quoted text clipped - 7 lines]

because of the large no. of PIDs...
any help wud be appreciated ...thank u...