>>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?