Performing calculations in Access Reports 1

[Tobasco1136]

I am trying to put a count of how many records there are on a report.

Title Count
Dr 100
Mr 50
Mrs 45
Ms 5

I want to display the total count in the Header.
Total Count: 200 which is a total of all of the counts.

I tried an unbound text box stating CountOfEventNumber as the control source. When I run the report, I get 100. If I move the text box to the footer, I get the bottom number of 5. I know this should be simple, but it isn't for me. Also, I don't know VBA so please don't use that.

After I get this count, I then want to add a column for % for each number of titles.

 

[dhookom]

Reports have 3 types of header sections. I assume you want a summary of titles in your report header.

First create a totals query similar to your report's record source that groups by title and counts titles:

SELECT [Title], Count([Title]) As TheCount
FROM [whatever]
GROUP BY [Title];

Use this totals query as the record source for a subreport that can be placed in the main report's header section.

Add a text box in the subreport footer (not page footer) with:
[tt][blue]
Name: txtSumCount
Control Source: =Sum(TheCount)
[/blue][/tt]
Add a text box in the detail section
[tt][blue]
Name: txtPctCount
Control Source: =[TheCount]/[txtSumCount]
Format: Percent
[/blue][/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

Where do I put this totals query? I'm really new to access.

 

[dhookom]

Use this totals query as the record source for a subreport that can be placed in the main report's header section.

A subreport is like any other report except that it will not display any page sections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

Ok, I finally figured the subreport out and built it. However, when I run the subreport, it asks me for the txtSumCount that I added into the details section. I know the txtSumCount in the footer works though.

 

[Tobasco1136]

ok, I finally figured out what the issue was. Thanks for your help.