>I've created a view to use for a report based on a Parent - Child - Grandchild arragement. I'm able to retreive the correct resultset.
>
>I need to count each parent's children and sum certain field values that also belong to each parent's children. In some cases, a parent's child may have more than one child (grandchild) and that's were I'm having difficulty. When a child has more than one child(grandchild), the resultset contains more than the actual number of children as follows:
>
>Parent1 - Child1 - Grandchild1
>Parent1 - Child1 - Grandchild2
>
>In the above case there is actually only one parent and only one child, but, because the child has two children(grandkids), it causes "duplicate" parents and children.
>
>The report prints correctly. The "duplicate" parents and children do not print, but both the count and sum vars double their results. For example in the above case, the Child1 count ends up being 2 instead of 1 and the summed values that should be 0.05 are 0.10. I'd appreciate any and all ideas on how to resolve this issue.

It would be nice if you can post your current SQL code (or prototype). I know that your problem can be solved with using derived tables, but I haven't tried this in a view, only in direct SQL commands - I think it will probably work the same in a view. Your VFP version has to be VFP9 for this to work, though.

I actually see the problem now - may be it would be easier just to add counters to the SQL? In your case in the report you need to count unique Parent or Child, right? There are some tricky techniques for this problem by Cathy Pountney, if you're using VFP Report Writer.

Perhaps this thread #1156251 can be of help, though I'm not sure.