Try converting your WHERE clause conditions into JOIN conditions.
Below is an example (guessing about the appropriate JOIN because you don't qualify the souce tables for the result columns.)

SELECT oJC.CurState as State, CMRView.*, Date, Mortgage, Source, ;
    ParentCode as Lender, extrWeek, extrYear ;
  FROM TranMstr inner join Lookups!Lender on TranMstr.Lender = Lender.Code ;
  Inner Join SiteMstr on TranMstr.PropID = SiteMstr.PropID ;
    and (between(date, {01/01/2001}, {06/30/2001}) ) ;
  Inner Join CMRView on SiteMstr.town = CMRView.town ;
    and between(Mortgage, 1, 1000000000)
  INTO TABLE \redp\work\CMR\CMR

>Hi everybody,
>
>I have a query, which executes too long (though I executed it through PCAnywhere, but it should not be a factor here):
>
>

SELECT oJC.CurState as State, CMRView.*, Date, Mortgage, Source, ;
>ParentCode as Lender, extrWeek, extrYear ;
>FROM TranMstr inner join Lookups!Lender on TranMstr.Lender=Lender.Code ;
>Inner Join SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
>Inner Join CMRView on SiteMstr.town=CMRView.town ;
> where (between(date,ctod('01/01/2001'),ctod('06/30/2001')) ) and between(Mortgage,1,1000000000)  INTO TABLE \redp\work\CMR\CMR_0FE18SEWQ

>
>Here is sys(3054) result:
>

>
>Using index tag Date to rushmore optimize table tranmstr
>Using index tag Mortgage to rushmore optimize table tranmstr
>Using index tag Deleted to rushmore optimize table tranmstr
>Rushmore optimization level for table tranmstr: full
>Using index tag Deleted to rushmore optimize table lender
>Rushmore optimization level for table lender: full
>Using index tag Deleted to rushmore optimize table sitemstr
>Rushmore optimization level for table sitemstr: full
>Rushmore optimization level for intermediate result: none
>Joining table tranmstr and table sitemstr using index tag Propid
>Joining intermediate result and intermediate result using temp index
>Joining table lender and intermediate result using temp index
>Selected 13446 records in 368.26 seconds
>        275
>        375
>        475
>        575
>        675
>        775
>        875
>        975

>
>BTW, what do the numbers (275, 375, etc.) show here?
>
>Do you have suggestions to optimize it (don't suggest to drop deleted tags :))?
>
>Thanks a lot in advance.