Autosum field in table?

  • Thread starter Thread starter MartinL
  • Start date Start date
M

MartinL

In a table I have 4 fields: totWeek1, totWeek2, totWeek3, totWeek4

I also have a 5th field in the table called MonthlyTotal and I would like
for it to be a locked field in my form but have it be the autosum of the 4
weekly totals.

Any suggestions are greatly appreciated.

Thanks!
 
In a table I have 4 fields: totWeek1, totWeek2, totWeek3, totWeek4

I also have a 5th field in the table called MonthlyTotal and I would like
for it to be a locked field in my form but have it be the autosum of the 4
weekly totals.

Any suggestions are greatly appreciated.

Thanks!
Click to expand...

1) As long as you are storing the individual 4 fields data there is no
need to store, in a table, the sum of those fields.
2) On your form you can add an Unbound control. Set it's control
source to:
=Nz([totWeek1],0) + Nz([totWeek2],0) + Nz([totWeek3],0) +
Nz([totWeek4],0)
The result should NOT be stored in any table.

Any time you need the total of the weeks data, simply re-calculate it,
in a report or on a form, as above, or in a query:
MonthlyTotal:Nz([totWeek1],0) + Nz([totWeek2],0) + Nz([totWeek3],0) +
Nz([totWeek4],0)

By the way, as most months have more than exactly 4 weeks in them, how
do you handle the extra days?
The use of Week1, Week2 in your field names leads me to suspect that
your database is not properly Normalized.
 
1) As long as you are storing the individual 4 fields data there is no
need to store, in a table, the sum of those fields.
2) On your form you can add an Unbound control. Set it's control
source to:
=Nz([totWeek1],0) + Nz([totWeek2],0) + Nz([totWeek3],0) +
Nz([totWeek4],0)
The result should NOT be stored in any table.

Any time you need the total of the weeks data, simply re-calculate it,
in a report or on a form, as above, or in a query:
MonthlyTotal:Nz([totWeek1],0) + Nz([totWeek2],0) + Nz([totWeek3],0) +
Nz([totWeek4],0)

By the way, as most months have more than exactly 4 weeks in them, how
do you handle the extra days?
The use of Week1, Week2 in your field names leads me to suspect that
your database is not properly Normalized.
Click to expand...

Fred, thanks a lot for your help. I will try it some time during the day
when I have a chance. The reason for having the 4 weeks is just to have an
average based on the 4 weeks which is 1 months worth of information so it
really isn't that important if any specific days were actually in a specific
month or not.

Thanks again!

Martin L.
 
Martin

If you are using a Microsoft Access table, there is no such thing. Although
they look like spreadsheets, Access tables are not.

If you are using SQL-Server, you can define a "calculated column".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Fred, thanks a lot for your help. I will try it some time during the day
when I have a chance. The reason for having the 4 weeks is just to have an
average based on the 4 weeks which is 1 months worth of information so it
really isn't that important if any specific days were actually in a specific
month or not.
Click to expand...

There are 52 weeks in a year (52 weeks and either one or two days more in
fact).

There are 48 weeks if you treat a month as having four weeks.

You WILL LOSE DATA.
 
Back
Top