making an Auto calculate Total

Hi,

I have a database that will be dealing with customer usage forecasts on stock. As such they will give 12 month projections on the stock they require.

I have a form that will be filled in on site by the sales rep, but is there a way to make the total field auto sum after update of the last month?

I have a table called tbl_forecast which has 12 months spaces to complete the forecast ( called Month 1, Month 2.... etc) and the total (being Total) and Blanket_Agreement_Number to make sure it sums only the record in question

My current attempt was to try and create an auto populate using a dsum


Can someone advise where it is going wrong??

You have stored your forecast data in the wrong way. You should have stored it vertically, and I personally would use a date field, something like;

Fields
Employee, Forecast, ForecastDate

Then you can get a total forecast over any period by simply summing the employees forecasts for any given date criteria.

You shouldn't store the total in the table, you should always calculate it, as any change in any months forecast would mean having to recalculate the overall total again and store it.

Hi Minty,

I know this isnt the way your meant to do it.

Unfortunately the rules don't apply to every situation, and this is one of them.

I need the information to be stored the way I have done it.

You need to put " " around you're whole first part of the DSum()
"[Month1]+[Month2]+[..."

This will make doing things very awkward for you going forwards - honest

this is what i did for my own game database to do a count for the main page to show how many i had

place the below in an unbound textbox