Determine dates and insert into table

[scoobyroo]

I need to be able to determine all the dates a worker receives a payment based on the start date of payment and the number of months the worker is supposed to receive the payments over and insert those dates into a table.

The form shows the employe number, the payment amount, the date payments should start and the number of months. (Example: Employee A receives $10,000 starting 1/1/2012 for 6 months.)

They want to hit a calculate button that will populate a table with all the months and what amount is received each month. They also want the amounts rounded to the nearest dollar (showing no cents) and the final payment will show whatever remains.

From my example above, I normally would have taken 10000/6 to give me 1,666.67 payments per month, but according to their specs they want to see
month 1 - 1,667.00
month 2 - 1,667.00
month 3 - 1,667.00
month 4 - 1,667.00
month 5 - 1,667.00
month 6 - 1,665.00


Here are the tables I currently have:

tbl_employees
employee_number
employee_name
payment_start_date
payment_terms
amount


tbl_payments
employee_number
pay_period
scheduled_payment

The database is in Access 2007.

I am not sure how to determine all the dates and insert them into a table. Do I need to do a loop? Any coding advice is appreciated.

 

[Andrzejek]

You may start with calculating like this:

Debug.Print Format(CInt(10000 / 6), "#0,000.00")

that will give you 1,667.00

In your example you will round it up, so your last payment will be less that all the other payments.
What would happen if you round it down? Your last payment will be grater than the 'regular' payments. Is that what you (they?) want?

Have fun.

---- Andy