Using "Sum" to add values from records in detail section

[Adam]

I have a report that contains a customer ID header, customer detail section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is called
"Text48"). In the footer section i am now trying to add up a total of every
record that shows up in the detail section like so =Sum([Text48]). I'm trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody help??

 

[Rob Parker]

Hi Adam,

You can't sum contents of unbound/calculated text controls; you can only sum
fields in the report's recordsource.

So, what you need in the group/report footer, to sum the items in the detail
section's unbound textbox, is the same expression in a Sum function:
=Sum(([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category
Point Value]))

In the Customer footer section, this will give the total for each group; in
the Report footer, it will give the total for all customer groups.

HTH,

Rob

 

[Evi]

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is called
"Text48"). In the footer section i am now trying to add up a total of every
record that shows up in the detail section like so =Sum([Text48]). I'm trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody

help??

 

[Adam]

its the later, the columns in a table (one for each set)...is there another
way to do this?

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is called
"Text48"). In the footer section i am now trying to add up a total of every
record that shows up in the detail section like so =Sum([Text48]). I'm trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody

help??

 

[Evi]

I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps] *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag the TotReps field from there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi

its the later, the columns in a table (one for each set)...is there another
way to do this?

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is called
"Text48"). In the footer section i am now trying to add up a total of every
record that shows up in the detail section like so =Sum([Text48]). I'm trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody

help??

 

[Adam]

should these types of caclulations be done in the queriy in stead of the
report so that i can always have it in the quiery before any report needs to
run?

I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps] *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag the TotReps field from there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi

its the later, the columns in a table (one for each set)...is there another
way to do this?

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail
section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound
control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is
called
"Text48"). In the footer section i am now trying to add up a total of
every
record that shows up in the detail section like so =Sum([Text48]). I'm
trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody
help??

 

[Adam]

should it always calculate in the query instead of the rprt? so that this way
the rrprt does not have to run in order for me to get the value?

I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps] *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag the TotReps field from there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi

its the later, the columns in a table (one for each set)...is there another
way to do this?

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail
section,
and customer ID footer. In the detail section i have 4 fields that i am
applying an equation to, and performing that in a 5th field unbound
control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is
called
"Text48"). In the footer section i am now trying to add up a total of
every
record that shows up in the detail section like so =Sum([Text48]). I'm
trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody
help??

 

[Evi]

If you use the query to perform calculations on a 'row', it makes it easier
to perform calculations in the report on the group footers and report
footers.

Evi

should it always calculate in the query instead of the rprt? so that this way
the rrprt does not have to run in order for me to get the value?

I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps] *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag the TotReps field from there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi

its the later, the columns in a table (one for each set)...is there another
way to do this?

:

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail
section,
and customer ID footer. In the detail section i have 4 fields that

i
am

applying an equation to, and performing that in a 5th field unbound
control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is
called
"Text48"). In the footer section i am now trying to add up a total of
every
record that shows up in the detail section like so =Sum([Text48]). I'm
trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody
help??

 

[Evi]

You don't HAVE to do it but in your case. it certainly will make life
easier.
If the calculation applies to a 'row' of data rather than a group of data
then I personally can't think of a case when it would work better in a
report than in a query. (Any exceptions, anyone?)

Having the results in a query also makes it available eg in other reports,
in code, in forms and in queries based on this query.

Evi

should it always calculate in the query instead of the rprt? so that this way
the rrprt does not have to run in order for me to get the value?

I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps] *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag the TotReps field from there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi

its the later, the columns in a table (one for each set)...is there another
way to do this?

:

Just as a matter of interest, Adam, is this report based on a Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

I have a report that contains a customer ID header, customer detail
section,
and customer ID footer. In the detail section i have 4 fields that

i
am

applying an equation to, and performing that in a 5th field unbound
control
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
Value]). When i view the report it gives me the correct number for this
calculation (the 5th field name where this calculation is happening is
called
"Text48"). In the footer section i am now trying to add up a total of
every
record that shows up in the detail section like so =Sum([Text48]). I'm
trying
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody
help??