Summing Calculated Fields

[Brian]

I have a continuouse form that uses fields for [StartDate] and [EndDate] to
calculate a duration. It then used that duration to to calculate the value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between the
dates. After calculating hour in each month I am simply multiplying hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column for
the M1 (Month #1) calculated field.

 

[Jeff Boyce]

If I recall, you need to "do the math" again in the footer. Even though (it
sounds like) you create a new control (?M1?), trying to use "=Sum([M1])"
doesn't seem to work.

Replace the "[M1]" in that expression with the same calculation(s) you did
to determine [M1]'s value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a continuouse form that uses fields for [StartDate] and [EndDate] to
calculate a duration. It then used that duration to to calculate the value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between the
dates. After calculating hour in each month I am simply multiplying hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column for
the M1 (Month #1) calculated field.

It will... if it's a calculated *field* (in the form's Query) instead of a
calculated *control* (textbox on the form).

Either do the calculation in a calculated field in the query or recapitulate
the entire expression in the Sum() call.

 

[Brian]

Thanks that worked well 4 out of 6 places. I have 6 unbound text boxes in
the footer of my form, each equating the sum of the column of calculating
text boxes ([M1] .... [M6]) in the detail section above.

The first 4 columns of summing boxes work with the basic equation shown
below (this is the actual equation from the 4th box).

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(3)),[est_hrs]-[M1]-[M2]-[M3],21.7*[Hrs_Wday]))

The Weird thing is that when I use the exact same format of equation in the
5th column summing box I get an error (this is the equation for the 5th box)

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(4)),[est_hrs]-[M1]-[M2]-[M3]-[M4],21.7*[Hrs_Wday]))


In fact when this one errors, I also get errors in summing boxes for columns
1 through 4, so this one screw them all up. Columns 1 - 4 all work fine when
I remove the control source for #5.

I can't figure out what is different, I hope someone can help?

If I recall, you need to "do the math" again in the footer. Even though (it
sounds like) you create a new control (?M1?), trying to use "=Sum([M1])"
doesn't seem to work.

Replace the "[M1]" in that expression with the same calculation(s) you did
to determine [M1]'s value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a continuouse form that uses fields for [StartDate] and [EndDate] to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between
the
dates. After calculating hour in each month I am simply multiplying hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column for
the M1 (Month #1) calculated field.

 

[Douglas J. Steele]

Might you be dealing with Null values somewhere? You should use the Nz
function to ensure that Nulls are converted to zeroes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks that worked well 4 out of 6 places. I have 6 unbound text boxes in
the footer of my form, each equating the sum of the column of calculating
text boxes ([M1] .... [M6]) in the detail section above.

The first 4 columns of summing boxes work with the basic equation shown
below (this is the actual equation from the 4th box).

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(3)),[est_hrs]-[M1]-[M2]-[M3],21.7*[Hrs_Wday]))

The Weird thing is that when I use the exact same format of equation in
the
5th column summing box I get an error (this is the equation for the 5th
box)

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(4)),[est_hrs]-[M1]-[M2]-[M3]-[M4],21.7*[Hrs_Wday]))


In fact when this one errors, I also get errors in summing boxes for
columns
1 through 4, so this one screw them all up. Columns 1 - 4 all work fine
when
I remove the control source for #5.

I can't figure out what is different, I hope someone can help?

If I recall, you need to "do the math" again in the footer. Even though
(it
sounds like) you create a new control (?M1?), trying to use "=Sum([M1])"
doesn't seem to work.

Replace the "[M1]" in that expression with the same calculation(s) you
did
to determine [M1]'s value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a continuouse form that uses fields for [StartDate] and [EndDate]
to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between
the
dates. After calculating hour in each month I am simply multiplying
hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column
for
the M1 (Month #1) calculated field.

 

[Brian]

I added the Nz to all of my equations for the summing fields but still the
Sum of M5 & M6 error out. Below I have pasted all six of my equations, I
hope someone can see something in the last two that I am missing.

Sum of
M1=Sum(IIf(Nz([Duration])<Nz([WdRemain]),Nz([est_hrs]),Nz([WdRemain])*Nz([Hrs_Wday])))
Sum of
M2=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(1)),Nz([est_hrs])-Nz([M1]),21.7*Nz([Hrs_Wday])))
Sum of
M3=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(2)),Nz([est_hrs])-Nz([M1])-Nz([M2]),21.7*Nz([Hrs_Wday])))
Sum of
M4=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(3)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3]),21.7*Nz([Hrs_Wday])))
Sum of
M5=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(4)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3])-Nz([M4]),21.7*Nz([Hrs_Wday])))
Sum of
M6=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(5)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3])-Nz([M4])-Nz([M5]),21.7*Nz([Hrs_Wday])))

Thanks

Might you be dealing with Null values somewhere? You should use the Nz
function to ensure that Nulls are converted to zeroes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks that worked well 4 out of 6 places. I have 6 unbound text boxes in
the footer of my form, each equating the sum of the column of calculating
text boxes ([M1] .... [M6]) in the detail section above.

The first 4 columns of summing boxes work with the basic equation shown
below (this is the actual equation from the 4th box).

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(3)),[est_hrs]-[M1]-[M2]-[M3],21.7*[Hrs_Wday]))

The Weird thing is that when I use the exact same format of equation in
the
5th column summing box I get an error (this is the equation for the 5th
box)

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(4)),[est_hrs]-[M1]-[M2]-[M3]-[M4],21.7*[Hrs_Wday]))


In fact when this one errors, I also get errors in summing boxes for
columns
1 through 4, so this one screw them all up. Columns 1 - 4 all work fine
when
I remove the control source for #5.

I can't figure out what is different, I hope someone can help?

If I recall, you need to "do the math" again in the footer. Even though
(it
sounds like) you create a new control (?M1?), trying to use "=Sum([M1])"
doesn't seem to work.

Replace the "[M1]" in that expression with the same calculation(s) you
did
to determine [M1]'s value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a continuouse form that uses fields for [StartDate] and [EndDate]
to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between
the
dates. After calculating hour in each month I am simply multiplying
hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column
for
the M1 (Month #1) calculated field.

 

[Jeff Boyce]

Nz([x]) returns, I believe, a zero if you don't specify. Usually, this
works well, but consider, for a moment, if you are dividing by ... ?!0?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I added the Nz to all of my equations for the summing fields but still the
Sum of M5 & M6 error out. Below I have pasted all six of my equations, I
hope someone can see something in the last two that I am missing.

Sum of
M1=Sum(IIf(Nz([Duration])<Nz([WdRemain]),Nz([est_hrs]),Nz([WdRemain])*Nz([Hrs_Wday])))
Sum of
M2=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(1)),Nz([est_hrs])-Nz([M1]),21.7*Nz([Hrs_Wday])))
Sum of
M3=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(2)),Nz([est_hrs])-Nz([M1])-Nz([M2]),21.7*Nz([Hrs_Wday])))
Sum of
M4=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(3)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3]),21.7*Nz([Hrs_Wday])))
Sum of
M5=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(4)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3])-Nz([M4]),21.7*Nz([Hrs_Wday])))
Sum of
M6=Sum(IIf(Nz([Duration])<(Nz([WdRemain])+(21.7)*(5)),Nz([est_hrs])-Nz([M1])-Nz([M2])-Nz([M3])-Nz([M4])-Nz([M5]),21.7*Nz([Hrs_Wday])))

Thanks

Might you be dealing with Null values somewhere? You should use the Nz
function to ensure that Nulls are converted to zeroes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks that worked well 4 out of 6 places. I have 6 unbound text boxes
in
the footer of my form, each equating the sum of the column of
calculating
text boxes ([M1] .... [M6]) in the detail section above.

The first 4 columns of summing boxes work with the basic equation shown
below (this is the actual equation from the 4th box).

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(3)),[est_hrs]-[M1]-[M2]-[M3],21.7*[Hrs_Wday]))

The Weird thing is that when I use the exact same format of equation in
the
5th column summing box I get an error (this is the equation for the 5th
box)

=Sum(IIf([Duration]<([WdRemain]+(21.7)*(4)),[est_hrs]-[M1]-[M2]-[M3]-[M4],21.7*[Hrs_Wday]))


In fact when this one errors, I also get errors in summing boxes for
columns
1 through 4, so this one screw them all up. Columns 1 - 4 all work
fine
when
I remove the control source for #5.

I can't figure out what is different, I hope someone can help?




:

If I recall, you need to "do the math" again in the footer. Even
though
(it
sounds like) you create a new control (?M1?), trying to use
"=Sum([M1])"
doesn't seem to work.

Replace the "[M1]" in that expression with the same calculation(s) you
did
to determine [M1]'s value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a continuouse form that uses fields for [StartDate] and
[EndDate]
to
calculate a duration. It then used that duration to to calculate
the
value
of [estimated_hrs] for each month over the range of months within
the
duration. I have all of that working fine but I when I try to
total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration
between
the
dates. After calculating hour in each month I am simply multiplying
hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the
column
for
the M1 (Month #1) calculated field.

 

[Brian]

Your response helped solve the basic problem but my application still has a
related issues.

I have now added calculating fields M1 through M9 in my query. Those simply
calculate the number of hours to be worked for months 1 - 9 of the specific
jobs.

The problem I now face is totaling up all of the hours for Jan, Feb, March,
etc for all jobs so I can plan my work force requirements. Since each job
can start during different months throughout the year M1 for one job might be
Jan, where as M1 for a different Job could be M10.

What I thought would be the answer would be to use logic on my form to
determine which M? (from the query) would be displayed for Jan, Feb, Mar and
so on.

Now I do not know how to sum the totals for each of those months if I can't
total the controls of a form in the footer. Can you help me figure this one
out?

I have a continuouse form that uses fields for [StartDate] and [EndDate] to
calculate a duration. It then used that duration to to calculate the value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between the
dates. After calculating hour in each month I am simply multiplying hrs x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column for
the M1 (Month #1) calculated field.

It will... if it's a calculated *field* (in the form's Query) instead of a
calculated *control* (textbox on the form).

Either do the calculation in a calculated field in the query or recapitulate
the entire expression in the Sum() call.

 

[Douglas J. Steele]

Hate to seem rude, but your table isn't properly designed. If you've got
fields named M1, M2, ... M9, you've got a repeating group, which is a
violation of database normalization principles.

Each month's worth of data should be a separate row in a second, related
table. Then, you won't have the kinds of issues you're experiencing.

For more information about redesigning your tables, see the list of
references Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Your response helped solve the basic problem but my application still has
a
related issues.

I have now added calculating fields M1 through M9 in my query. Those
simply
calculate the number of hours to be worked for months 1 - 9 of the
specific
jobs.

The problem I now face is totaling up all of the hours for Jan, Feb,
March,
etc for all jobs so I can plan my work force requirements. Since each job
can start during different months throughout the year M1 for one job might
be
Jan, where as M1 for a different Job could be M10.

What I thought would be the answer would be to use logic on my form to
determine which M? (from the query) would be displayed for Jan, Feb, Mar
and
so on.

Now I do not know how to sum the totals for each of those months if I
can't
total the controls of a form in the footer. Can you help me figure this
one
out?

I have a continuouse form that uses fields for [StartDate] and [EndDate]
to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between
the
dates. After calculating hour in each month I am simply multiplying hrs
x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column
for
the M1 (Month #1) calculated field.

It will... if it's a calculated *field* (in the form's Query) instead of
a
calculated *control* (textbox on the form).

Either do the calculation in a calculated field in the query or
recapitulate
the entire expression in the Sum() call.

 

[Brian]

I can see how you might think that, but M1, M2, M3 ...M9 are just query field
names representing Project Month #1, Project Month #2 etc. The data for each
is calculated simply from a grand total number of hours, start date, end
date, and crew size. The only data/field that is fixed is the total number
of hours, everything else is a variable to schedule with and the M1....M9
fields are calculated by spreading the hours by using the start, end and crew.

The problem I am running into come from the fact that M1 for one Project
might be Jan, where as M1 for another might be June. They each have
different start months and end months. I am trying to figure out a form
design that I can total all of Jan, Feb, Mar etc to see the total man power
requirements for each calendar month.

So I think my design is ok, just my application is complicated. However,
you are the expert and I am just looking for assistance so I appreciate your
comments.

Hate to seem rude, but your table isn't properly designed. If you've got
fields named M1, M2, ... M9, you've got a repeating group, which is a
violation of database normalization principles.

Each month's worth of data should be a separate row in a second, related
table. Then, you won't have the kinds of issues you're experiencing.

For more information about redesigning your tables, see the list of
references Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Your response helped solve the basic problem but my application still has
a
related issues.

I have now added calculating fields M1 through M9 in my query. Those
simply
calculate the number of hours to be worked for months 1 - 9 of the
specific
jobs.

The problem I now face is totaling up all of the hours for Jan, Feb,
March,
etc for all jobs so I can plan my work force requirements. Since each job
can start during different months throughout the year M1 for one job might
be
Jan, where as M1 for a different Job could be M10.

What I thought would be the answer would be to use logic on my form to
determine which M? (from the query) would be displayed for Jan, Feb, Mar
and
so on.

Now I do not know how to sum the totals for each of those months if I
can't
total the controls of a form in the footer. Can you help me figure this
one
out?

On Wed, 14 Jan 2009 09:42:11 -0800, Brian
<[email protected]>
wrote:

I have a continuouse form that uses fields for [StartDate] and [EndDate]
to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration between
the
dates. After calculating hour in each month I am simply multiplying hrs
x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column
for
the M1 (Month #1) calculated field.


It will... if it's a calculated *field* (in the form's Query) instead of
a
calculated *control* (textbox on the form).

Either do the calculation in a calculated field in the query or
recapitulate
the entire expression in the Sum() call.

 

[Douglas J. Steele]

It doesn't matter whether M1 represents Jan or June. Am I correct in
assuming that if M1 represents Jan, then M2 represents Feb, M3 represents
March and so on?

Even if it doesn't, the fact that M1 has a variable meaning is indicative to
me that the design could be better.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I can see how you might think that, but M1, M2, M3 ...M9 are just query
field
names representing Project Month #1, Project Month #2 etc. The data for
each
is calculated simply from a grand total number of hours, start date, end
date, and crew size. The only data/field that is fixed is the total
number
of hours, everything else is a variable to schedule with and the M1....M9
fields are calculated by spreading the hours by using the start, end and
crew.

The problem I am running into come from the fact that M1 for one Project
might be Jan, where as M1 for another might be June. They each have
different start months and end months. I am trying to figure out a form
design that I can total all of Jan, Feb, Mar etc to see the total man
power
requirements for each calendar month.

So I think my design is ok, just my application is complicated. However,
you are the expert and I am just looking for assistance so I appreciate
your
comments.

Hate to seem rude, but your table isn't properly designed. If you've got
fields named M1, M2, ... M9, you've got a repeating group, which is a
violation of database normalization principles.

Each month's worth of data should be a separate row in a second, related
table. Then, you won't have the kinds of issues you're experiencing.

For more information about redesigning your tables, see the list of
references Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Your response helped solve the basic problem but my application still
has
a
related issues.

I have now added calculating fields M1 through M9 in my query. Those
simply
calculate the number of hours to be worked for months 1 - 9 of the
specific
jobs.

The problem I now face is totaling up all of the hours for Jan, Feb,
March,
etc for all jobs so I can plan my work force requirements. Since each
job
can start during different months throughout the year M1 for one job
might
be
Jan, where as M1 for a different Job could be M10.

What I thought would be the answer would be to use logic on my form to
determine which M? (from the query) would be displayed for Jan, Feb,
Mar
and
so on.

Now I do not know how to sum the totals for each of those months if I
can't
total the controls of a form in the footer. Can you help me figure
this
one
out?



:

On Wed, 14 Jan 2009 09:42:11 -0800, Brian
<[email protected]>
wrote:

I have a continuouse form that uses fields for [StartDate] and
[EndDate]
to
calculate a duration. It then used that duration to to calculate the
value
of [estimated_hrs] for each month over the range of months within the
duration. I have all of that working fine but I when I try to total
the
columns of hours calculated for each month I am getting #Error.

I am using DateDiff with "d" interval to calculate the duration
between
the
dates. After calculating hour in each month I am simply multiplying
hrs
x
days = hrs per month.

In the footer of the form I thought =Sum([M1]) would total the column
for
the M1 (Month #1) calculated field.


It will... if it's a calculated *field* (in the form's Query) instead
of
a
calculated *control* (textbox on the form).

Either do the calculation in a calculated field in the query or
recapitulate
the entire expression in the Sum() call.

 

[John W. Vinson]

Your response helped solve the basic problem but my application still has a
related issues.

I have now added calculating fields M1 through M9 in my query. Those simply
calculate the number of hours to be worked for months 1 - 9 of the specific
jobs.

The problem I now face is totaling up all of the hours for Jan, Feb, March,
etc for all jobs so I can plan my work force requirements. Since each job
can start during different months throughout the year M1 for one job might be
Jan, where as M1 for a different Job could be M10.

What I thought would be the answer would be to use logic on my form to
determine which M? (from the query) would be displayed for Jan, Feb, Mar and
so on.

Now I do not know how to sum the totals for each of those months if I can't
total the controls of a form in the footer. Can you help me figure this one
out?

Perhaps rather than doing the calculation in two steps - calculating M1
through M9 and then trying to add up M1, M2, and so on - could you perhaps use
DSum() to add up the nine months of data directly from the table, or from the
underlying query? You could use the DSum expression as the control source of a
report textbox.