Thanks for your reply, Eric.

Problem is, after building the query as you suggest, the only way I know to fill table is with SCAN, which is very inefficient if there is a large amount of data.

Is there a pure SQL way that anybody knows?

Here is an example to make my question clearer.

Parent table (batches table):
Batch #  Date and other info used to filter query
1        15/01/2000 etc.
2        31/01/2000
etc...

Child table (transactions table)
Batch #  Trans #   Customer #
1         1           200
1         2           150
1         3           210
1         4           050
2         5           300
2         6           310
3         7           200
etc...

Grandchild table (detail table)  There are dozens of categories.
Trans #   Category Name  Quantity
1         RegTime        8.00
1         Factor1_25     2.00 
1         Factor1_50     1.00 
2         RegTime        2.00 
3         RegTime        7.50
3         Absence        0.50
etc...

Desired result:
Batch #   Trans #      RegTime    Factor1_25    Factor1_50   Absence
1         1            8.00       2.00          1.00         0.00
1         2            2.00       0.00          0.00         0.00
1         3            7.50       0.00          0.00         0.50
etc...

Thanks.

Alex

>Build a select string by opening the table and adding to the select like:
>x = "select "
>scan
>x = x+" "+field+" AS "+field &&Blah blah blah...
>endscan
>
>Place a loop here to see if the table contains anything from the known list, anything not in the table add to the select string as a place holder.
>
>x = x+"0 AS Value, XX AS FieldName from table into table mytable"
>
>&x
>
>This should give you the names and corresponding values and zero for the others.
>
>HTH

>Hi,
>
>Have parent/child/grandchild tables. Grandchild table may contain records that contain a category with its corresponding quantity. Whenever there is no grandchild record with a given category, it means its quantity is zero.
>
>We know aheard of time all possible categories.
>
>The question is: how do you efficiently create a table (via SQL SELECT's, ideally) that contains one record for each child member and that includes a column for each possible category with with the correct quantity in it.
>
>TIA,
>
>Alex