>>>>>Hello
>>>>>I have three table with a common field. Can I set the relation between these three tables and their common field? I am used to doing
>>>>>this with 2 but not 3. Is there some way I can establish a "relationship" between 12 tables, each of which has a common field.
>>>>
>>>>I find using SQL and cursors much easier than anything involving SET RELATION, SET SKIP etc. I haven't used the latter commands for many years.
>>>
>>>I still use them to verify in first step visually if I generated large amounts of data - second step is generating descriptive stats across linked tables. Often you see your gotchas in the browse relationships test without having to wait for heavy join action ;-)
>>
>>I have 12 tables, actually more, and each one has a common field, called mDate, in this format: 201211281730, which includes the year, the month, the day, the hour, and the
>>minute (in 15 minute segments). In addition, each table has a numeric field, called mRating. What I really want to do is to get the sum of all of the mRating's fields, for each particular
>>hour. I really am not sure how to do this with sql. The following must be a step in the wrong direction and will take a really, really long time.
>>
>>

>>SELECT table1.mRating + table2.mRating as SumRating FROM table1, table2
>>

>>
>>Is there a better quicker way to do this with SQL? I know I can use set relation with 2 tables. But the help in VFP 9 does not say much about additive, although I
>>suppose it must be what it sounds like.
>>
>>Thanks, Steve
>
>

>CLOSE ALL
>SELECT 1
>USE table4
>INDEX on mindex TO g
>SELECT 2
>USE table5
>INDEX on mindex TO gg
>SELECT 3
>USE table6
>INDEX on mindex TO ggg
>SELECT 4
>USE table7
>INDEX on mindex TO gggg
>SELECT 5
>USE table8
>INDEX on mindex TO o
>SELECT 6
>USE table9
>INDEX on mindex TO oo
>
>SELECT 1
>SET RELATION TO mindex INTO table5 additive
>SET RELATION TO mindex INTO table6 additive
>SET RELATION TO mindex INTO table7 additive
>SET RELATION TO mindex INTO table9 additive
>SET RELATION TO mindex INTO table10 additive  && after 4 set relation's, foxpro says the following: Table10 is not found
>

Can you explain exactly what do you want to do? The code above is very bad and it should not be used in the real program.

I think you want something along these lines:

select NVL(T1.RatingSum,0) + NVL(T2.RatingSum) as TotalRating, NVL(T1.yymmddhh, T2.yymmddhh) AS yyMMddhh

FROM (select sum(mRating) as RatingSum, LEFT(mDate,10)  as yyMMddhh  from Table1 GROUP BY 2) T1

FULL JOIN (select sum(mRating) as RatingSum, LEFT(mDate,10)  as yyMMddhh  from Table2 GROUP BY 2) T2

ON T1.yymmddhh = T2.yymmddhh

You can get your sums from each table into cursors and then join all these cursors together for your final result to get total sum.