CREATE SQL VIEW "VIEW_ELEMENTS" ; 
   AS SELECT Elements.clm_ref, Elements.total_amt, Elements.agreed_amt,;
  Elements.admitted_amt, Elements.setoff_amt, Elements.rejected_amt,;
  Elements.multiplier_used, Elements.unquantified,;
  Elements.date_element_received, Elements.claim_elem_code,;
  Elements.claimstatus, Elements.excludingcosts, Elements.withdrawn,;
  Elements.wdn_amt, Elements.wdn_date,;
  Elements.adm_in_lux, Elements.id, Elements.potential,;
  Elements.reject_date, Elements.rej_wave_no, Elements.appeal_date,;
  Elements.struck_o_date, Elements.struck_o_desc, Elements.stg_let_st,;
  Elements.setoff_date, Elements.agreed_date, Elements.admitted_date,;
  Elements.stg_let_rc, Elements.in_rejection_amt,;
  Elements.court_order_date, Elements.in_rejection_letter_date,;
  Elements.in_rejection_letter_post_type,;
  Elements.in_rejection_narrative, Elements.waiver_flag,;
  Elements.waiver_date, Elements.appeal_expiry_date,;
  Elements.appeal_expired_flag, Elements.appeal_expiry_term,;
  Elements.litigant, Elements.luxrej_dt, Elements.location,;
  Elements.amend_date, Elements.amend_id,Elements.wdn_code,;
  Elements.total_amt-(Elements.agreed_amt+Elements.rejected_amt+Elements.in_rejection_amt+Elements.wdn_amt) AS pending_amt,;
  .F. AS lchanged,;
  VAL(TRANSFORM(IIF(Elements.agreed_amt>0,Elements.agreed_amt-Elements.setoff_amt,0),"999999999.99")) AS post_setoff,;
  Elemtype.elem_desc, Wdntypes.wdn_desc ;
  FROM employee!elements INNER JOIN employee!elemtype;
    INNER JOIN employee!wdntypes ;
   ON Elements.wdn_code = Wdntypes.wdn_code ;
   ON Elements.claim_elem_code = Elemtype.elem_code ;
 WHERE Elements.clm_ref = ?Thisform.clm_ref ;
 ORDER BY Elemtype.elem_desc



sorry the query is so lengthy, I know I can't create this view using the designer but !!!, why do I get the error message "SQL Column: WDN_CODE not found" when I try to create the view programatically?, this code worked fine until I added the third table "Wdntypes" into the equation, I posted a similar thread last week and learnt it was a failing of the view designer, so I duly coded this, and it doesn't work, the problematic field "WDN_CODE" is present in both tables and is of the same data type



regards

Pete Kane

Regards,
Peter J. Kane



Pete