Spread One Amount in a single Control across 52 weeks

[Nevsky78]

Dear all,

I have attached a simplified database highlighting my issue - AnnualSpread.mdb.

I have users who create a project and enter one amount for the cost of that project for the whole year. For financial purposes I have to automatically spread that one amount equally across 52 weeks.

In the attached database I have created what will be the form used when creating a new project.

Would anybody now the coding required on the after update event to automatically spread the amount in that control across 52 weeks in "tbl_project_week"?

I think I have the structure right with a table for each project (tbl_project), for the weeks (tbl_weeks) and the table that would create 52 records for each week against each project (tbl_project_weeks)..

The number of weeks in tbl_weeks will never change.

If I can get help with the new record form, I should be able to work out myself how to have a form that enables the 'yearly' amount to be changed after initial set up (with an update query).

Any help would be very gratefully received!

Thanks,
Nick

 

[Nevsky78]

After all that I worked out how to do it myself!

Please find solution attached.

Nick