Microsoft Access: Showing a sum total of number of records in a Report

[eberry707]

Hello:

I have been trying to figure out how to automatically show a "sum total" of the number of records (from the underlying query) in report form.

For example, if the design view of a query lists 25 records, how can i visually show the "25" number in a report that is linked to this query?

Thank you

[BradHodge]

You can add a text box to your report. In the Control Source Property, put...

=DCount("MyQueryName", "MyPrimaryField")

That will add the number of records to your text box when the report runs. If you want to then format it for eye appeal, you could do something like this in the Control Source...

="Total Records in this Report = " & DCount("MyQueryName", MyPrimaryField")

Hope that helps,
Brad.

[eberry707]

Brad:

The name of my underlying query is "qrydataentryerrors" and the primary key for the underlying table is "ID".

I created a text box in the report footer and added =DCount([qrydataentryerrors],[ID]) to the control source.

That did not work. Am I missing something? Is the syntax incorrect?

Thank you,

Eric

[BradHodge]

Brad:

The name of my underlying query is "qrydataentryerrors" and the primary key for the underlying table is "ID".

I created a text box in the report footer and added =DCount([qrydataentryerrors],[ID]) to the control source.

That did not work. Am I missing something? Is the syntax incorrect?

Thank you,

Eric

Eric,

For one, I steered you wrong. My apologies. You should have your field name listed before your query name.

Second, I would suggest using quotes around your field name and your table instead of brackets.

It should look something like this...

DCount("ID", "qrydataentryerrors")

It helps when someone doesn't tell you to turn right when you are supposed to be turning left :)

Brad.

[pollyanna]

Hi there. I have a question in this area. I hope you don't mind me chiming in.

I am summing records. Each month I want to sum my record count by month and year to date (YTD); providing both report counts at the end.

I am using this for monthly and it works fine:

="Monthly Summary for " & "'IS #' = " & " " & [IS #] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"

I don't understand what to write to sum the record count YTD.

Can you help?

[pollyanna]

Hi there. I have a question in this area. I hope you don't mind me chiming in.

I am summing records. Each month I want to sum my record count by month and year to date (YTD); providing both report counts at the end.

I am using this for monthly and it works fine:

="Monthly Summary for " & "'IS #' = " & " " & [IS #] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"

I don't understand what to write to sum the record count YTD.

Can you help?

From thescripts resources I found “Count Data on a Report & summarize it at the end”-beacon & Jim Doherty" through this I realized that I had a footer thing going on. As soon as I moved all of the summing elements that were incorrectley summing into a subsequent footer the suming behaves the way I want it to. Glad to know I have that back up.

But, what if multiple footers sum & counting on one page?