Access 2007 - How to calculate from form fields and store in a table

[scoobyroo]

Using Access 2007.

Requirement: Access database that tracks what dollar amount worker must receive every month based on start date, bonus value, and number of months. A worker can receive a bonus to be paid over a number of months (the max is usually 60 months).

The input screen contains the bonus amount, the date payments should start and the number of months. (Example: Tom Smith receives $100,000 starting 2/14/2012 for 60 months.)

They want to hit a calculate button that will populate a table with all the months and what amount is received each month.

The data is currently kept in a spreadsheet and is set up as follows (Note: I have only included 12 of the 60 months):


Worker Bonus # of months effective date 2/14/2012 3/14/2012 4/14/2012 5/14/2012 6/14/2012 7/14/2012 8/14/2012 9/14/2012 10/14/2012 11/14/2012 12/14/2012 1/14/2013
Tom Smith 100,000 60 2/14/2012 1,667 1,667 1,667 1,667 1,667 1,667 1,667 1,667 1,667 1,667 1,667 1,667


I haven't used Access in years and have no idea how to go about doing something like this. Any suggestions are greatly appreciated.

 

[MazeWorX]

If they receive an equal payment on the same day of every month then its seems redundant to store each months data? Something like Total payment, Start date and the number of equal payments should do the trick the rest can be calculated. As far as setting up tables you should look up the subject matter 'Normalized Data'. There's plenty of info on the net. The calculations can be done by query or calculated controls when a form is opened or populated by a selection or setup to run a report whatever your personal preference is

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

How are ya scoobyroo . . .
[ol][li]First thing to do is learn to think db (not spreadsheet) when dealing with a database. Too many spreadsheet officianado's try to turn Access into Excel ... bad idea![/li] Note: Excel itself is referred to as a [blue]flat line[/blue] database.
[li]In a db, calculations are not typically stored in a table ... they're simply calculated and displayed. Why store something that should calculate out anyway. For youe secnario I'd have something like:

[blue][tt]StartDate RegularPayments EndDate FinalPayment
********** *************** ********** ************
12/10/2011 1666.66 11/10/2016 $1667.06[/tt][/blue][/li][/ol]
I think thats a much better [blue]birds eye view[/blue] than scrollong thru 60 redundant column's ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I am going to assume you actually need a record for each months payment. Maybe this is the plan and you can then alter from there, or maybe there is other unique fields for a month payment. You then need two tables.

tblBonuses
BonusID (primary key uniquely identifying a bonus for a pers)
PersonID_FK (relates back to the person table)
dtmStartDate (start date)
paymentPeriods (number of payments)

the above table is the input form. Once you input a record you can then write the code to run an insert query into the following table. Creating a record for each payment. It could then show as a subform on the main form.

tblBonusPayments
BonusID_fk (foreign key to the bonus table)
dtmPayPeriod (date of payment)
currPayment (Proposes payment = amount / periods)
...other unique fields for the payment if exists
currActualPayment (maybe need to track what changes from the proposes)

 

[scoobyroo]

Unfortunately, when I suggested calculations on the fly in the report, it didn't go very well. They want to store the data so they can go back and look at prior years if they ever needed to. They also want to be able to make edits (for example if someone terminates and they pay out before the scheduled end month).

 

[MazeWorX]

Which can all be accomplished with calculations at run time

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>