calculations on reports

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

I have a report that is working pretty well, it does sums
of various fields, but I need to do some tweaking, and I
am not sure how. One question is this...I have the
report doing a sum of different counties, so if the field
shows washington, it will count it as one, if not, it is
a zero....here is an example:
=Sum(IIf([County]="Washington",1,0))

My question is this: how would I say the same thing, only
wanting it to count all the clients in washington,
carver, and ramsey?

THe other question is how do i count the total number of
people in the report. The query runs for a specified
month, and then the report counts all the info in the
query and spits out the numbers in individual catagories,
but I want to find out the total number of clients, so I
know that there should be a total of x number of clients.

Example the report says
above poverty 33
below poverty 45

ramsey county 54
carver county 24

In each case, those number should equal the total number
of people in my query, so how to I know what the total
number in my query actually is?

--Deb
 
I'm not sure what you mean by "doing a sum of different counties", but I am
going to guess that you are referring to people above and below the poverty
line. If so, sorting and Grouping together with Running Sum will probably
suit your needs. With the report open in design view, click View > Sorting
and Grouping, and Group by your county field. Short explanation: select your
county field in the dialog box, and change Group Header and/or Group Footer
to Yes. See Help for more information.
For the running sum I can't offer much detail without knowing more about how
your database is set up, but generally speaking if you had a number of
records for a county, all grouped together as described, and if there is a
number field in each individual record, you can use the text box's property
sheet to set the text box (let's call it txtOverPoverty) containing that
number to Running Sum Over Group. You could also make txtOverPoverty
invisible, and in the group footer you could have another text box (visible)
with the control source =txtOverPoverty.
To be any more specific or do do any troubleshooting it will be necessary to
know more about your database structure.
 
I would first add a field into your table of counties that identifies the
reason these particular counties should be included in the count and other
counties such as Hennepin, Anoka, and Scott should not. This would allow you
to use data rather than complex expressions to "filter" your expressions.
tblCounties
===================
CountyName GroupX
Anoka No
Carver Yes
Hennepin No
Ramsey Yes
Scott No

Add this table to your query and join the CountyName fields. Then your
expression in the report would be something like
=Sum( Abs(GroupX = True) )

If you want to display groups of counties together, you may need to create
subreports.
 
Back
Top