Show certain records, but total ALL records

[Jewlrose]

I have information for various counties in a table. I want the report to show 11 specific counties. Originally, I wrote this into the criteria section of the query (countyname = "county1" OR "county2" OR ... OR "county11"). However, I also want to have a sum of the data in the report header for the entire state.

So...

State: SumOfData

County1: County1 Data
County2: County2 Data
...
County11: County11 Data

Does anyone know of a way to specify which records show in a report, but still have the a sum of ALL the information?

Thanks in advance!!

 

[boblarson]

For all records just put a control in there and use a DSum to calculate:

=DSum("[FieldNameHereToSum]", "TableNameHere")

 

[Jewlrose]

Bob,

Thank you so much for your reply, I'm not certain I quite understand. I currently have a query that merges and calculates information from multiple tables/queries and a report which displays the information in that query. That query currently limits the counties to a specific list of counties and orders them alphabetically. If I read your reply correctly, I believe you are indicating I should use DSum to give me the total for the state. However, if I use DSum, won't it total the results of the query (which is limited to only those 11 records) and thus not give me a state-wide total? If I take the criteria limitation off of the query, it would then show all 88 counties instead of only the 11 that I want- though the State-wide sum would be correct.

Thanks again.

 

[boblarson]

If you use the DSum in the control source of a single text box on the report and do not use the query in the DSum, but instead use the actual table, you should get the complete total of all of the states and not just the ones from the record source of the report. That control would likely go in the Report Footer.

 

[Jewlrose]

That worked perfectly!! Thank you so much!