Hi Diana,

I've just re-read the whole thread and now I see your problem. The solution is simple: Order your table by Primary+ID+AgeGroup (this is a scheme, you should translate it to the valid index expression, e.g. use Bintoc or str function, if applicable.

Now you can design your report:

Group1 - by Primary
 SubGroup - by Client ID
    SubGroup  - by AgeGroup
Detail  - data
    SubGroup Footer  - Children/Adults count           
  SubGroup Client visits
Group Total - by Primary Insurance 
Report Toital ----------------------

Does it make sense?


>Thanks, Nadya for this technique.  I learned a lot by setting it up and using it.  However, I'm still only getting total visits from this technique, not unique clients.  I think it's got to look something like:
>
>select nprimary, sum(  I think I need a select DISTINCT statement here, but I don't know how to do that)....
>
>
>Diana
>
>>>The data looks something like this:
>>><pre>
>>>ID  Visit Date   Primary Insurance  Age
>>>___ __________   _________________  ___
>>>1	12/19/00	BC			12
>>>1	12/20/00	BC			12
>>>1	12/21/00	BC			12
>>>2	12/18/00	MC			3		
>>>2	12/19/00	MC			3
>>>3	12/17/00	007			24
>>>4	12/17/00	BC			15
>>>4	12/18/00	BC			15
>>>4	12/19/00	BC			15
>>>5	12/18/00	BC			36
>>>5	12/19/00	BC			36
>>>

>>>The Report looks like this:
>>>
>>>Insurance report - clients by sponsor
>>>

>>>BC
>>>	Children:
>>>		1	12/19/00
>>>		1	12/20/00
>>>		1	12/21/00
>>>		4	12/17/00
>>>		4	12/18/00
>>>		4	12/19/00
>>>	Total Children (visits): 6
>>>
>>>	Adults:
>>>		5	12/18/00
>>>		5	12/19/00
>>>	Total Adults (visits): 2
>>>
>>>Total BC Visits: 8
>>>

>>>etc...
>>>
>>>What I want to do is include in the subtotals the unduplicated counts. The ID refers to a client, so the subtotals above would be 2 children under BC and 1 adult under BC. I understand how to do an unduplicated count outside of the report using the count(distinct id) concept on the cursor I'm using for the report and just adding that variable to my report, but I would only then get a count of the individual clients at the bottom of my report. I'm wondering how to get the subtotals to show unduplicated counts. Using an array of variables that I calculate outside of the report seems to be the way to go, but I don't know how to let the report writer know which group is which variable (IE BC, 007, etc.)
>>>
>>>Diana
>>
>>
>>Hi Diana,
>>
>>As I said, prepare data before hyand, e.g.:
>>

>>select Primary, sum(iif(age<18,1,0)) as ChildVisits, ;
>>       sum(iif(age>=18,1,0)) as AdultVisits;
>>       from Visits ;
>>       group by Primary, ;
>>       into cursor curTotal
>>
>>select * from Visits inner join curTotal on Visits.Primary=curTotal.Primary ;
>>        into cursor curFinal && Here I'm not 100% sure, you may use Lookup function instead
>>

>>
>>Now you have all data, and designing report would be very easy.