Query to disperse amount over months

[thart21]

Could someone please tell me what I should search on in the forum to find this type of query.

I have a db that keeps track of cost savings. Each record "cuts in" in a different month. I have a calculated amount from two fields in my table. I need to take that total divide by 13 - the month number i.e. July=7, and update the month fields by that amount beginning in that month (July) I have the calculation for each month figured out but need to know how to disperse it out to the individual months from that beginning month to December.

Example: Total cost savings $50,000 Cut-in month July (7) Each month's cost savings is $50k/(13-7) = July - $8,333 Aug - $8,333 Sep - $8,333 through December.

Thanks!

 

[RichO]

I'm confused by your calculation:

Example: Total cost savings $50,000 Cut-in month July (7) Each month's cost savings is $50k/(13-7) = July - $8,333 Aug - $8,333 Sep - $8,333 through December.

July's amount would be $8,333, but if I understand your explanation, shouldn't Aug be $10,000 ($50k/(13-8)), and Sep $12,500 ($50k/(13-9))?

Are you wanting to update a field in a table with this calculation, using a query?

If you are doing a basic calculation, why update the table when you can always do the calculation in a form or report. It's best to do calculations "on the fly" and display them in unbound controls rather than storing them.

 

[thart21]

Thanks for the reply. The monthly amount in my example is basically $50K/6 (13-7=6). Each month has the same amount in it-the calculation is not done for each month, just once. My intention is to calculate this on the fly via a command button fon my form for each record involved and not to put it in a table-my mention of "fields" was meant for the query fields. Each record will have a different "cut-in" month and a different amount to calculate the monthly figure.

Thanks,

Toni

 

[RichO]

So then exactly what are you needing information on? Are you wanting to know how to do this calculation in a query so your form has an immediate field containing the calculation? You can also do any calculation from the code beneath a command button.

Are you looking to simply divide a figure by 6 and nothing more?

 

[thart21]

I have the code to figure out the calculation. What I need is to know how to tell access that I want to see an unbound text box for each month on a form with that monthly amount that is calculated. I will have a form that will have a text box for each month-Jan, Feb,Mar, etc. If the cut-in month is March, I want the calculated monthly amount to show up in March through December with null values in Jan and Feb.

Thanks

 

[RichO]

OK I get it. Are your months displayed as a continuous form or do you have a separate text box for each month?

 

[thart21]

My form will show 12 text boxes, one for each month, it is not a continuous form. I want the user to click on a command button, have that form pop-up to display all 12 months and the monthly savings amount.

Toni

 

[RichO]

I drew up 2 sample forms for what you might want, it was pretty simple. I found it easier to use VBA to do the calculation rather than a query. As an example, open the CutInForm first and enter the cut in month and the total savings, then the command button will open the 2nd form.

 

[thart21]

RichO,

This worked perfectly! Thanks so much for getting me through this one!

 

[thart21]

Got another one along the same lines and would appreciate a point in the right direction. I need to now take the amount in 1 column [Reduction in Cost] for all records and, based on the [CutInMonthNumber] disperse that amount through each month and add up the totals for each month. I want to have a command button on my menu to open a form which runs this query. What type of query do I need to create for this?

Thanks