Summing a data field for the whole report

[Jerry]

I have a db with two tables, documents and transactions. A document can have
many transactions. I have two numerical fields in the documents table which
are populated for each document, no matter how many transactions. I have a
report for each company where the document is listed with it's unique data
and is sorted and grouped with a header and footer, and below the header in
the detail section I list all transactions associated with that document. I
want to sum those two fields at the end of the report. But when i create the
text box with the "=sum([Manhrs])" in the report footer for the ManHrs field,
the total I get is as if the manhours recorded for each document is recorded
for each transaction; ie if i had only one document in the report, and the
document had two transactions and the ManHrs field value in the document
table = 3, the total at the end of the report would be 6, not 3. How do i
get this sum to be correct?
thanks
Jerry Bennett
Easthampton, MA

 

[Jeff Boyce]

Jerry

If I'm understanding your data design, your [Manhrs] could belong to
document1 or document2. When you use the Report footer to put
=sum([Manhrs]), you'll get them all (if you asked for them all in the
underlying query).

If you only want document1's [Manhrs], you could always divide your report
into a Doc1 and Doc2 section (GroupBy document#), then do the =sum(...) in
the footer of that section.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry]

Thanks for the reply Jeff, but i guess i wasn't too clear. I have ONE
document table and ONE transaction table; a document can have many
transactions, but a transaction can only be attached to ONE document. ManHrs
is a field in the document table. The report lists the document with it's
unique fields across the page, and underneath across the page are the
transactions attached to the document. Manhrs is a field from documents, so
it is on the documents line. But the total in the report footer sections
acts like manhrs is a data field for each transaction.

Jerry

If I'm understanding your data design, your [Manhrs] could belong to
document1 or document2. When you use the Report footer to put
=sum([Manhrs]), you'll get them all (if you asked for them all in the
underlying query).

If you only want document1's [Manhrs], you could always divide your report
into a Doc1 and Doc2 section (GroupBy document#), then do the =sum(...) in
the footer of that section.


Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a db with two tables, documents and transactions. A document can
have
many transactions. I have two numerical fields in the documents table
which
are populated for each document, no matter how many transactions. I have
a
report for each company where the document is listed with it's unique data
and is sorted and grouped with a header and footer, and below the header
in
the detail section I list all transactions associated with that document.
I
want to sum those two fields at the end of the report. But when i create
the
text box with the "=sum([Manhrs])" in the report footer for the ManHrs
field,
the total I get is as if the manhours recorded for each document is
recorded
for each transaction; ie if i had only one document in the report, and the
document had two transactions and the ManHrs field value in the document
table = 3, the total at the end of the report would be 6, not 3. How do i
get this sum to be correct?
thanks
Jerry Bennett
Easthampton, MA

 

[Jeff Boyce]

Jerry

So you're saying the [Manhrs] belongs to a single document, and you want the
sum of all the [Manhrs] in your document table.

Try taking a look at the DSum() function in Access HELP. I suspect you'll
be able to use that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the reply Jeff, but i guess i wasn't too clear. I have ONE
document table and ONE transaction table; a document can have many
transactions, but a transaction can only be attached to ONE document.
ManHrs
is a field in the document table. The report lists the document with it's
unique fields across the page, and underneath across the page are the
transactions attached to the document. Manhrs is a field from documents,
so
it is on the documents line. But the total in the report footer sections
acts like manhrs is a data field for each transaction.

Jerry

If I'm understanding your data design, your [Manhrs] could belong to
document1 or document2. When you use the Report footer to put
=sum([Manhrs]), you'll get them all (if you asked for them all in the
underlying query).

If you only want document1's [Manhrs], you could always divide your
report
into a Doc1 and Doc2 section (GroupBy document#), then do the =sum(...)
in
the footer of that section.


Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a db with two tables, documents and transactions. A document can
have
many transactions. I have two numerical fields in the documents table
which
are populated for each document, no matter how many transactions. I
have
a
report for each company where the document is listed with it's unique
data
and is sorted and grouped with a header and footer, and below the
header
in
the detail section I list all transactions associated with that
document.
I
want to sum those two fields at the end of the report. But when i
create
the
text box with the "=sum([Manhrs])" in the report footer for the ManHrs
field,
the total I get is as if the manhours recorded for each document is
recorded
for each transaction; ie if i had only one document in the report, and
the
document had two transactions and the ManHrs field value in the
document
table = 3, the total at the end of the report would be 6, not 3. How
do i
get this sum to be correct?
thanks
Jerry Bennett
Easthampton, MA