Hi Abel,

The view itself is a bit hard to digest so I try to draw something here

  Parent (LibraryICQIntroductions) - PK  Field cpk_libicqintro
            Child (LibraryICQQuestion)  FK - cfk_libicqintro PK - cpk_libicquestions
                  GrandChild (LibraryICQTasks) FK - cfk_libicquestions

Select Parent.Fields, ChildCount.CountChildren, Child.* Fields, 
GrandChildrenCount.CountGrandChildren, Grandchild.Fields from
Parent LEFT JOIN (select Child.ParentFK, 
count(distinct(ChildrenPK)) as CountChildren from Child group by 1) 
CountChildren on Parent.PK = CountChildren.ParentFK
LEFT JOIN Child ON Parent.PK = Child.ParentFK 
LEFT JOIN (select GrandChild.ChildPK, count(distinct(ChandChildPK) from GrandChild group by 1)  
 CountGrantChildren on Child.ChildPK = CountGrantChildren.ChildFK 
LEFT JOIN GrandChild ON Child.ChildPK = GrandChild.PK where some conditions

This is an idea to put counts into the same query as derived tables.

>Hi Naomi. Thanks for replying. I'm using VFP 9.0 SP2. Here's the view code that I created using XCase as the modeling tool and then synch'ed it with VFP. I use Visual FoxExpress (VFE) as the VFP framework. The view is used to print one or more Questionnaires. LibraryICQIntroductions is the parent with a primary key of Libraryicqintroduction.cpk_libicqintro. It holds various memo fields (Introduction, Purpose, Scope, Instructions). LibraryICQQuestions is a child of LibraryICQIntroductions with a foreign key of Libraryicqquestions.cfk_libicqintro and a primary key of Libraryicqquestions.cpk_libicquestions. It holds each Introduction's actual questions. It also holds a memo field named ICQStatement. Lastly, LibraryICQTasks is a child of LibraryICQQuestions with a foreign key of Libraryicqtasks.cfk_libicquestions. It holds a memo field named TaskDescription. I need to count each LibraryICQIntroduction's questions and sum each question's ICQTime. I'm fairly new to SQL and have been teaching myself in an accelerated form in order to resolve this. I recreated the view without the memo fields to avoid conflicts with the DISTINCT clause but it didn't help. I ran into the same "duplicate" columns issue. Is there a way to include COUNT(Libraryicqquestions.cfk_libicqintro) and sum(Libraryicqquestions.icqtime) in the existing code? Finally, two related questions; per SQL books I've been studying; am I correct in understanding that all columns in a SELECT statement MUST appear in the GROUP BY clause?

If the fields are not part of some AGGREGATE function such as MAX, MIN, AVG, COUNT, SUM, then they must appear in the GROUP BY clause.

if so must they be in the same order? And, Must all columns in a SELECT statement be in every SELECT statement that is UNIONed? I aks because I though of spliting the SQL into various sections but if the UNION question is true, then it won't work.
>


If you want to UNION two selects, then fields and their types (size) must much.



>Please excuse my limited SQL knowledge and please accept my sincere thanks for your time and effort..
>
>SELECT Libraryicqintroduction.cpk_libicqintro,;
> Libraryicqintroduction.icqno, Libraryicqintroduction.icqtype,;
> Libraryicqintroduction.versiondate, Libraryicqintroduction.icqtitle,;
> Libraryicqintroduction.introduction,;
> Libraryicqintroduction.icqpurpose, Libraryicqintroduction.icqscope,;
> Libraryicqintroduction.instructions,;
> Libraryicqintroduction.creator AS creator1,;
> Libraryicqintroduction.createdatetime AS createdatetime1,;
> Libraryicqquestions.cfk_libicqintro,;
> Libraryicqquestions.cpk_libicquestions,;
> Libraryicqquestions.icquestionno, Libraryicqquestions.icqtime,;
> Libraryicqquestions.icqstatementsummary,;
> Libraryicqquestions.icqstatement, Libraryicqquestions.comments,;
> Libraryicqquestions.icqresponse,;
> Libraryicqquestions.creator AS creator0,;
> Libraryicqquestions.createdatetime AS createdatetime0,;
> Libraryicqtasks.cfk_libicquestions, Libraryicqtasks.cpk_libicqtasks,;
> Libraryicqtasks.icqtaskno, Libraryicqtasks.taskbudhours,;
> Libraryicqtasks.taskdescription, Libraryicqtasks.tasksummary,;
> Libraryicqtasks.creator, Libraryicqtasks.createdatetime;
> FROM ;
> AMS!LibraryICQTasks ;
> RIGHT OUTER JOIN AMS!LibraryICQQuestions ;
> ON Libraryicqtasks.cfk_libicquestions = Libraryicqquestions.cpk_libicquestions ;
> RIGHT OUTER JOIN AMS!LibraryICQIntroduction ;
> ON Libraryicqquestions.cfk_libicqintro = Libraryicqintroduction.cpk_libicqintro;
> ORDER BY Libraryicqintroduction.icqno,;
> Libraryicqquestions.icquestionno, Libraryicqtasks.icqtaskno
>