Sum of DCounts

  • Thread starter Thread starter David F
  • Start date Start date
D

David F

I have a DCount included with each row in the details section of a report.

Now I want to sum all these DCounts up in the footer of the report.

What is the best way to sum the values of all these DCount txtboxes in my
report?
 
David said:
I have a DCount included with each row in the details section of a report.

Now I want to sum all these DCounts up in the footer of the report.

What is the best way to sum the values of all these DCount txtboxes in my
report?
Click to expand...

If the Dcount were in the report's record source query, you
could use the Sum function, but if it's in a text box in the
report, then you need to use the text box's RunningSum
property.
 
In the details section of my report I have a textbox named "txtNote" that
has the following control source:


=DCount("[note]![sourceID]","[note]","[note]![sourceID]=Reports![r_Sources]!
[txtSourceID]")

This gives me a count of all the notes associated with a particular source.

Now I want to sum all of the notes for all sources. So in the page footer
section of the report I add a text box named "txtSumNotes" and give it the
following control source:

=Sum([txtNote]).

I then set the Running Sum property for this text box to "Over All".

But the textbox displays in my report as "#Error."

Can you tell what I am doing wrong?
 
I would create a totals query
SELECT SourceID, Count(*) as NumOfNotes
FROM Note
GROUP BY SourceID;
Include this query in your report's record source and join the SourceID
fields. You can then add the NumOfNotes field to the grid and your report
will be much more efficient.


--
Duane Hookom
MS Access MVP


David F said:
In the details section of my report I have a textbox named "txtNote" that
has the following control source:


=DCount("[note]![sourceID]","[note]","[note]![sourceID]=Reports![r_Sources]!
[txtSourceID]")

This gives me a count of all the notes associated with a particular source.

Now I want to sum all of the notes for all sources. So in the page footer
section of the report I add a text box named "txtSumNotes" and give it the
following control source:

=Sum([txtNote]).

I then set the Running Sum property for this text box to "Over All".

But the textbox displays in my report as "#Error."

Can you tell what I am doing wrong?
Click to expand...
 
Thanks Duane

But how do I add a second record source to my report?

The report is based on an existing query. How can I reference a second
query to get my count?



Duane Hookom said:
I would create a totals query
SELECT SourceID, Count(*) as NumOfNotes
FROM Note
GROUP BY SourceID;
Include this query in your report's record source and join the SourceID
fields. You can then add the NumOfNotes field to the grid and your report
will be much more efficient.


--
Duane Hookom
MS Access MVP


David F said:
In the details section of my report I have a textbox named "txtNote" that
has the following control source:
Click to expand...
=DCount("[note]![sourceID]","[note]","[note]![sourceID]=Reports![r_Sources]!
[txtSourceID]")

This gives me a count of all the notes associated with a particular source.

Now I want to sum all of the notes for all sources. So in the page footer
section of the report I add a text box named "txtSumNotes" and give it the
following control source:

=Sum([txtNote]).

I then set the Running Sum property for this text box to "Over All".

But the textbox displays in my report as "#Error."

Can you tell what I am doing wrong?



Marshall Barton said:
David F wrote:

I have a DCount included with each row in the details section of a report.

Now I want to sum all these DCounts up in the footer of the report.

What is the best way to sum the values of all these DCount txtboxes
Click to expand...
Click to expand...
in
Click to expand...
 
I maybe wasn't clear. You have a query that is the record source of your
report. You can add tables/queries to your query and join fields as I
suggested.

--
Duane Hookom
MS Access MVP


David F said:
Thanks Duane

But how do I add a second record source to my report?

The report is based on an existing query. How can I reference a second
query to get my count?



Duane Hookom said:
I would create a totals query
SELECT SourceID, Count(*) as NumOfNotes
FROM Note
GROUP BY SourceID;
Include this query in your report's record source and join the SourceID
fields. You can then add the NumOfNotes field to the grid and your report
will be much more efficient.
Click to expand...
=DCount("[note]![sourceID]","[note]","[note]![sourceID]=Reports![r_Sources]!
[txtSourceID]")

This gives me a count of all the notes associated with a particular source.

Now I want to sum all of the notes for all sources. So in the page footer
section of the report I add a text box named "txtSumNotes" and give it the
following control source:

=Sum([txtNote]).

I then set the Running Sum property for this text box to "Over All".

But the textbox displays in my report as "#Error."

Can you tell what I am doing wrong?



David F wrote:

I have a DCount included with each row in the details section of a
report.

Now I want to sum all these DCounts up in the footer of the report.

What is the best way to sum the values of all these DCount txtboxes
Click to expand...
in
my
report?

If the Dcount were in the report's record source query, you
could use the Sum function, but if it's in a text box in the
report, then you need to use the text box's RunningSum
property.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top