Part of your problem may be the indices on Deleted(). Try to work without them. Read my FAQ #8109 for an explanation, why this index might interfere in the speed.

HTH, Hilmar.

>I'm current using 5.0a, but trying to find the time to upgrade to 7.0.
>
>I believe that SQL is my best option here, but certainly open to other ideas.
>
>My problem is that no matter what I try, the SQL takes too long to find what I'm looking for.
>I've tried setting Exclusive on for tables being used.
>I've tried adding/removing indexes.
>I've tried packing the tables.
>The SQL is still taking too long, and getting longer as we add more records.
>
>
>I have 2 tables
>
>

>Table1
>Field           Type      Notes
>-------------------------------
>PK1             I         PK
>Emp             I         FK
>Flag            L
>Amt             Y
>PK1_Emp_FLAG    C(21)     Value set to str(PK1)+str(Emp)+Flag in record validation()
>Cust            I         FK
>Type            I         FK
>
>Indexes
>-------
>PK1                   Primary
>Emp                   Regular
>Flag                  Regular
>PK1_Emp_Flag          Candidate
>Cust_Type             Candidate [ str(Cust)+str(Type) ]
>Deleted               Regular
>
>
>Table2
>-------------------------------
>PK2             I         PK
>PK1             I         FK (related to Table1.PK1)
>Emp             I         FK (not necessary same value as in Table1)
>Flag            L
>PK1_Emp_FLAG    C(21)     Value set to str(PK1)+str(Emp)+Flag in record validation()
>Date            D
>Amt             Y
>
>Indexes
>-------
>PK2                   Primary
>PK1                   Regular
>Emp                   Regular
>Flag                  Regular
>PK1_Emp_Flag          Candidate
>PK1_Emp_Flag_Date     Candidate [ PK1_Emp_Flag+dtos(Date) ]
>Deleted               Regular
>

>
>For Each PK1_Emp_Flag:
>Table1.Amt = sum(Table2.Amt)
>There MAY NOT be a matching PK1_Emp_Flag in Table1 for each PK1_Emp_Flag in Table2.
>
>

>Possible Scenario:
>Table1
>------
>PK1     Emp     Flag    Amt
>------------------------------
>1       1       .T.     $25
>2       3       .F.     $7
>
>Table2
>------
>PK2     PK1     Emp     Flag     Date        Amt
>------------------------------------------------
>1       1       2       .F.      {3/1/2}     $10
>2       1       1       .T.      {3/1/2)     $15
>3       1       1       .T.      {3/8/2)     $5
>Note: there is no matching record in Table1 for Emp=2
>
>I Need to Add these records to Table2:
>4       1       2       .F.      {3/15/2}   -$10
>5       1       1       .T.      {3/15/2}    $5
>6       2       3       .F.      {3/15/2)    $7
>

>
>On {3/15/2} I need to:
>1. Find PK1=1, Emp=2, Flag=.F., sum(Amt)=$10, so I can Add record 4.
>2. Find PK1=1, Emp=1, Flag=.T., sum(Amt)=$20, so I can Add record 5.
>3. Find PK1=2, Emp=3, Flag=.F., sum(Amt)=.null., so I can Add record 6.
>
>

>The SQL's I'm currently using are:
>
>To Locate Info to create record 4:
>Select Table2.PK1_Emp_Flag, Sum(Table2.Amt) as Paid, Table1.PK1_Emp_Flag ;
>    from Table2 left join Table1 ;
>    on Table2.PK1_Emp_Flag = Table1.PK1_Emp_Flag ;
>    group by 1 ;
>    having ;
>    isnull(Table1.PK1_Emp_Flag) ;
>    and Paid <> 0
>
>To Locate Info to create records 5 & 6:
>Select Table1.PK1_Emp_Flag, Table1.Amt, ;
>    sum(iif(isnull(Table2.Amt),0,Table2.Amt)) as Paid ;
>    from Table1 left join Table2 ;
>    on Table1.PK1_Emp_Flag = Table2.PK1_Emp_Flag ;
>    group by 1 ;
>    having ;
>    Table1.Amt <> Paid
>