>I have 4 tables that I need to join into one big table for export purposes and historic value.
>
>Request
>Recievable
>Payor
>Name
>
>Recievable is the main table which can have many records attached to it from Request. Payor and Name tables are one to one connection to Recievable table.
>
>Can anyone suggest a way to speed this up?
>
>Thanks,
>Beth

Beth,

How about something like this:

SELECT * ;
  FROM Payor JOIN Name ;
    ON Payor.Name_Id = Name.Name_Id ;
  JOIN Receivable ;
    ON Payor.Payor_Id = Receivable.Payor_Id ;
  JOIN Request ;
    ON Payor.Payor_Id = Request.Payor_Id ;
 WHERE Payor.Payor_Id = lcID ;
  INTO CURSOR ExportData

Regards,

>

>SELECT * FROM payor WHERE payor_id = lcid ;
>  INTO CURSOR ctemp nofilter
>lncount1 = _TALLY
>IF lncount1 > 0
>  lncount = 0
>  SELECT lastname, firstname, midname, edulogid FROM name1 ;
>    WHERE edulogid IN (SELECT name_id FROM payor WHERE payor_id = lcid ) ;
>    INTO CURSOR cname1 nofilter
>  lncount = _TALLY
>ENDIF
>SELECT 'paid'
>SCATTER NAME loPaid MEMO BLANK
>loPaid.recstatus = pcStatus
>IF lncount > 0
>  SELECT 'cname1'
>  GOTO 1
>  loPaid.first_name = cname1.firstname
>  loPaid.last_name = cname1.lastname
>  loPaid.mid_name = cname1.midname
>  loPaid.name_id = cname1.edulogid
>ENDIF
>IF lncount1 > 0
>  SELECT 'ctemp'
>  GOTO 1
>  loPaid.account_no = ctemp.account_no
>  loPaid.bank_code = ctemp.bank_code
>  loPaid.bank_name = ctemp.bank_name
>  loPaid.branchcode = ctemp.branch_code
>  loPaid.modepaymnt = ctemp.mode_payment
>  loPaid.rib_code = ctemp.rib_code
>ENDIF
>SELECT 'lcRequest'
>FOR lnloop = 1 TO lnrow
>  GOTO lnloop
>  IF lnloop = 1
>    loPaid.COMMENT = ALLTRIM(lcRequest.COMMENT)
>    loPaid.costamount = lcRequest.cost_amount
>    loPaid.create_src = lcRequest.create_source
>    loPaid.key_id = lcRequest.key_id
>    loPaid.last_updte = lcRequest.last_update
>    loPaid.payer_id = lcRequest.payer_id
>    loPaid.PROGRAM = lcRequest.PROGRAM
>    loPaid.rate_code = lcRequest.rate_code
>    loPaid.requestdte = lcRequest.request_date
>    loPaid.request_id = lcRequest.request_id
>    loPaid.requesttyp = lcRequest.request_type
>    loPaid.serviceno = lcRequest.serviceno
>    loPaid.STATUS = lcRequest.STATUS
>    loPaid.statusdate = lcRequest.status_date
>    loPaid.student_id = lcRequest.student_id
>    loPaid.tran_mode = lcRequest.tran_mode
>    loPaid.transtat = lcRequest.transtat
>    loPaid.trnsttcmnt = lcRequest.transtat_comment
>    loPaid.trnsttdte = lcRequest.transtat_date
>  ELSE
>    STORE ALLTRIM(lcRequest.COMMENT) TO ("loPaid.COMMENT" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.cost_amount TO ("loPaid.CSTAMONT" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.create_source TO ("loPaid.CRTE_SRC" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.key_id TO ("loPaid.KEY_ID" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.last_update TO ("loPaid.LST_UPDT" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.payer_id TO ("loPaid.PAYER_ID" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.PROGRAM TO ("loPaid.PROGRAM" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.rate_code TO ("loPaid.rate_cde" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.request_date TO ("loPaid.RQUSTDTE" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.request_id TO ("loPaid.RQUST_ID" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.request_type TO ("loPaid.RQUSTTYP" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.serviceno TO ("loPaid.srviceno" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.STATUS TO ("loPaid.STATUS" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.status_date TO ("loPaid.STTUSDTE" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.student_id TO ("loPaid.STDNT_ID" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.tran_mode TO ("loPaid.TRN_MDE" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.transtat TO ("loPaid.TRANSTAT" + PADL(lnloop, 2, '0'))
>    STORE ALLTRIM(lcRequest.transtat_comment) TO ("loPaid.TRNSTCMT" + PADL(lnloop, 2, '0'))
>    STORE lcRequest.transtat_date TO ("loPaid.TRNSTTDT" + PADL(lnloop, 2, '0'))
>  ENDIF
>ENDFOR
>*!*	  ADD the recievable info
>STORE poReceiv.CHK_PAYD TO loPaid.CHK_PAYD
>STORE ALLTRIM(poReceiv.COMMENTS) TO ("loPaid.COMMENTS")
>STORE poReceiv.CONF_CLT TO ("loPaid.CONF_CLT")
>STORE poReceiv.DTE_1STMOD TO ("loPaid.DTE_1STMOD")
>STORE poReceiv.DTE_AUTH TO ("loPaid.DTE_AUTH")
>STORE poReceiv.DTE_DUE TO ("loPaid.DTE_DUE")
>STORE poReceiv.DTE_NACT TO ("loPaid.DTE_NACT")
>STORE poReceiv.DTECREATE TO ("loPaid.DTECREATE")
>STORE poReceiv.DUP_AMOUNT TO ("loPaid.DUP_AMOUNT")
>STORE poReceiv.DUPLICATE TO ("loPaid.DUPLICATE")
>STORE poReceiv.END_DATE TO ("loPaid.END_DATE")
>STORE poReceiv.NXT_ACT TO ("loPaid.NXT_ACT")
>STORE poReceiv.PAY_MODE TO ("loPaid.PAY_MODE")
>STORE poReceiv.PAYDELETED TO ("loPaid.PAYDELETED")
>STORE poReceiv.REC_CAT TO ("loPaid.REC_CAT")
>STORE poReceiv.REC_STAT TO ("loPaid.REC_STAT")
>STORE poReceiv.receivable_id TO ("loPaid.RECVBL_ID")
>STORE poReceiv.RECEIVED TO ("loPaid.RECEIVED")
>STORE poReceiv.ST_DATE TO ("loPaid.ST_DATE")
>STORE poReceiv.STDDELETED TO ("loPaid.STDDELETED")
>STORE poReceiv.STUT_DTE TO ("loPaid.STUT_DTE")
>STORE poReceiv.TOT_AMT TO ("loPaid.TOT_AMT")
>STORE poReceiv.TRAN_ANUM TO ("loPaid.TRAN_ANUM")
>STORE poReceiv.TRAN_BNUM TO ("loPaid.TRAN_BNUM")
>STORE poReceiv.USERNAME TO ("loPaid.USERNAME")
>STORE poReceiv.payer_id TO ("loPaid.RPAYER_ID")
>STORE poReceiv.PRELEVDATE TO ("loPaid.PRELEVDATE")
>SELECT 'paid'
>LOCATE FOR recvbl_id = loPaid.recvbl_id
>IF !FOUND()
>  APPEND BLANK
>ELSE
>  llok = checkfordelete(loPaid.recstatus, loPaid.recvbl_id, ;
>    loPaid.PAY_MODE)
>ENDIF
>GATHER NAME loPaid
>llok = replacenulls('paid')
>
>