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