The DISTINCT does not work. In my example here (in UT message) I simplified the SQL Select. In actual case, the SUM is of a product of several fields.
Thank you for your suggestion.
>Try to add DISTINCT
>...,NVL(SUM(DISTISNCT MyTable2.Hours),00000.00) as TotHrs,
>>NVL(SUM(DISTISNCT MyTable3.Cases),00000.00) as TotCases
>...
>
>
>>I have the following SQL that sometimes gives me erroneous results:
>>
>>
>>select GROUP_FLD, NVL(SUM(MyTable2.Hours),00000.00) as TotHrs,
>>NVL(SUM(MyTable3.Cases),00000.00) as TotCases from MyTable1
>>join MyTable2 on MyTable2.pk_fld = MyTable1.pk_fld
>>join MyTable3 on MyTable3.pk_fld = MyTable1.pk_fld
>>group by GROUP_FLD
>>
>>
>>What happens is that if Table3 has 2 records and Table2 has 1
>>records, total of 2 two records are selected before VFP starts to group and SUM() the records. And the SUM(MyTable2.Hours) shows duplicate number of hours (as it calculates based on two records). Whereas in fact it should be on 1 record.
>>
>>Is the only solution to the above problem to move the SUM(...) to SUBQUERIES?
>>
>>Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
