>>>>How would you select a sum of fields from 2 child tables for the parent key?
>>>>
>>>>Here is the pseudo code of what I need:
>>>>
>>>>select (sum(hrs*rate) from Labor where Labor.ifkey=Job.ipkey) + (sum (qty*price) from Material where Material.ifkey=Job.ipey) as TotalCost
>>>
>>>Using derived tables (VFP9 only), of course.
>>>

>>>select cast(NVL(LaborTotal,0) + nvl(MateralTotal,0) as N(10,2)) as Total 
>>>from (select IfKey, sum(hrs*rate) as LaborTotal from Labor  group by 1) Labor 
>>>FULL JOIN (select ifKey, sum(qty*Price) as MaterialPrice from Material group by 1) Material on Labor.ifkey = Material.ifkey

>>
>>Looks promising, I'm gonna try it right now.
>>
>>One thing I forgot to mention is, I need this total to be one field in the resulting cursor and there will but other fields too, like this:
>>
>>JobNo Total
>>===== ==============
>>124345 12,510.65
>>643328 52,710.83
>>
>>
>>Like this:
>>
>>

>>Select Job.JobNo, (What Naomi said) as Total from Job where job.status='Active'
>>

>>
>>Will that work?
>
>Something like this
>

>Select Job.JobNo, cast(NVL(LaborTotal,0) + nvl(MateralTotal,0) as N(10,2)) as Total from Jobs FULL OUTER JOIN (material select) Material
>on Job.JobKey = Material.JobKey FULL OUTER JOIN (Labor select) Labor on Job.JobKey = Labor.JobKey where Job.Status = 'Active'

Here's what works fine, thanks to you. Notice your part betweren the arrows in the code below. You have helped me very much here.

Now, let me say that I am already calculating the Material cost and Labor cost as separate summed fields, but I also want to add them togther in a separate column. So, how can you just just add fields that are not natural fields in the tables, but they are derived fields in the SQL (using the "as" clause). You will see " (Select sum(...) ) as Material" and " (Select sum(...)) as Labor" in the code below. It seems crazy to have to re-calculate both of those child data sets again, just to add them together, when I've already done that for each of them sepatetely in the preceeding SQL.

Select job_item.job_num,
       job_info.job_start as date,
       job_info.p_o_num as po_num,
       job_item.qty,
       job_item.part_no,
       nvl(parts.altpartno,'') as Alt_Part_No,
       job_item.dwg_no,
       nvl(parts.desc, job_item.desc) as descrip,
       job_item.price,
       job_item.qty*job_item.price as total,
       job_info.quote_no,
       job_info.est_cost,
       (Select Cast(Sum(mat_qty*mat_cost*job_info.mat_mkup) as N(10,2)) From Material Where mat_code=Job_Info.job_num) as Material,
       (Select Cast(Sum(hrs_worked*mach_rate) as N(10,2)) From Actijobs Where job_num=Job_Info.job_num) as Labor,
       (Select Cast(Sum(hrs_worked) as N(10,2)) From Actijobs Where job_num=Job_Info.job_num) as Hours,
---->  (Select cast(NVL(LaborTotal,0) + nvl(MaterialTotal,0) as N(10,2)) 
|         From (Select job_num as lbrkey, sum(hrs_worked*mach_rate) as LaborTotal from Actijobs  group by 1) Labor
|         Full Join (Select mat_code as mtlkey, sum(mat_qty*mat_cost*Job_Info.mat_mkup) as MaterialTotal from Material group by 1) Material on Labor.lbrkey = Material.mtlkey
---->     Where Labor.lbrkey=Job_Info.job_num) as TotalCost,
       job_info.job_invnum as Inv_Num,
        (Select Sum(amount) From Job_Invoice Where job_num=Job_Info.job_num) as Inv_Amt,
       job_info.cust_num,
       space(6) as Vendor
       From job_item
             left outer join Job_Info on job_item.job_num=job_info.job_num
             left outer join Parts on job_item.ipartkey=parts.ipkey
...    (where, order by, and into clause goes here, dynamically added)