=count is not right

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

I have a database that keeps track of clients. i have
several tables. i want to run a report that will show how
many clients in a given date were terminated with the
outcome of the termination (a client may choose to leave
the program and come back within the same month). I
created a qry that has clientSocialSecurityNo, termination
date, termination outcome and created a report based on
it. I put it so that the SSNO is grouped and it will show
the ssno, name and termination dates and outcomes. in the
report footer i put =Count(qryTerminationAndOutcome!
ClientSSNo), so that it can give me a total number of
clients terminated,

the problem is that is a client leaves and comes back the
same month, it is counting him/her twice. how can i do it
so that it will count the SSNo once. In the report, i
want it to show how many times a client left and came
back, but in the rpt header, i want it to count the SSNo,
once
 
You can group by SSNO and add a group header. In the header, add a text box
Name: txtCount
Control Source: =1
Runnning Sum: Over All
Then in your report footer, add a text box
Control Source: =txtCount
If you don't like having the space taken the the header, you can use code to
in its On Format event
Me.MoveLayout = False
 
Back
Top