Count function in report

[mccaulj]

I am having trouble finishing up the design for a report. I need to count total number of individuals who received a service. The problem is that the person could have received service in one or more areas. I have it set up now as =Count[ID], but that is counting the ID of those who received service in more than one area, more than once. Is there a way to limit the count to the first occurrence of the ID, or some other way to not count duplicate ID's?
Thanks so much!!!

 

[mccaulj]

PLEASE help me. I just can't make this work. I have read at least 4 books on Access and can't find anything to help me. If anyone has any ideas please let me know!!

 

[David R]

Group your report by service, then count in the group footer. It will only count those records that appeared in the detail section, as far as I know.

HTH,
David R

 

[mccaulj]

Thank you, but I think that creates a whole new problem. If I sort that way, all the people that received that service would be listed, but they would be listed for each service so when I went to total them it would still be counting duplicates.
I need to leave it sorted by ID for other reasons as well (totals, sums, etc)
My report is Sorted by ID that then lists services received in the detail. I need to get a number of ID's that are found on the report w/o it counting duplicates. For instance ID#1 sought assistance for service one and service two. The report is set up to read:
ID#1 Bob
service one 2 hours
Service two 2 hours
When I use the count function as it is now (See above), it would return "2" as the number of id's rather than just "1".
Does that help anyone help me????
Thanks again to all who read and have ideas!!

 

[Rich]

Either try hiding duplicates on the ID field or use a sub query SELECT DISTINCTROW

 

[Pat Hartman]

Add a hidden control to the Id group header. Set its controlsource to 1 and its running sum property to "over all" and its Name to txtIdCount:

Visible ......... No
Name ............ txtIdCount
ControlSource ... =1
RunningSum ...... Over All

Then add an unbound control to the report footer and make its control source:

ControlSource ... =txtIdCount

 

[mccaulj]

THANK YOU!!!!!
This works and seems so simple.

J.McCaul