>>I've been trying to figure out an SQL-SELECT statement to perform
>>the following with no luck. I want to generate a list of the total
>>number of child records different parent records have.
>>
>>Let me explain...
>>
>>Say we have 4 related tables, where Table1 is the parent of Table2,
>>and Table2 is the parent of Table3, and Table3 is the parent of Table4.
>>All these tables have a 1-to-many relationship with their children;
>>that is, Table1 can have one or more Table2 records associated with it,
>>Table2 can have one or more Table3 records associated with it, etc.
>>
>>And let's say the child tables have a foreign key named after its parent
>>table. So Table2 has a field called fk_Table1, and Table3 has a field
>>called fk_Table2.
>>
>>Now. I want to generate a list that appears like so:
>>
>> Table1 Table2 Table3 Table4
>>=========================================
>> Value1 2 3 7
>> Value2 8 1 4
>>
>>where each column represents the number of records for the parent table.
>>So, the first row above means that Value1 in Table1 has 2 children in
>>Table2. And those 2 children from Table2 have a total of 3 children in
>>Table3. And those 3 children from Table3 have a total of 7 associated
>>records in Table4.
>>
>>Get it? Hope I explained this okay.
>>
>>Thanks one and all,
>>
>>Jody Cairns
>>cairns@athena.nwafc.nf.ca
>
>For 2 tables:
>SELECT Table1.fk_Table1, count() AS CntTable2;
> FROM Table1, Table2;
> INTO CURSOR Temp1;
> WHERE Table1.fk_Table1 = Table2.fk_Table1
>
>Probably you can run several SELECTs like this and combine the results.
>
>Vlad

There was a thread a couple weeks ago about counts and sums in 3 or 4 level parent-child relations, which finally decided you must do each level separately.

Jody, you'll probably need to SELECT 3 and 4 together, then 2 & the 3-4 cursor and finally the 1 and 2-3-4 cursor.

Barbara