>>Can someone help me to create a view?
>>
>>I have one table(one side) that holds customer numbers, program description, indicates wheather or not the program is in process, indicates if the program is closed, program manager, hours to complete, and comments. From this table I need to know which programs are in process and not closed.
>>
>>My second table(many side) lists the milestone task that each customer number requires, baseline date to complete, adjusted date to complete, percentage complete.
>>
>>From this table I need two records for each customer number, each task named "Load Common Structures" and "Load Final Structure". Then only records where the "Load Final Structure" is not 100% complete.
>
>Something like this :
>
>select * ;
> from force t1 inner join t2 on t1.custno=t2.custno and ;
> inlist(t2.task,"Load Common Structures","Load Final Structure") ;
> where
>t1.custNo in ;
>(select custNo from t2 where ;
>Task == "Load Final Structure" and pctComplete < 100)
Once you are satisfied with the results add 'create SQL view viewName as ' in front of select.
>Cetin
This might be a designer friedly version :
select <fieldList> ;
from t1 ;
inner join t2 on t1.custno=t2.custno and ;
t2.task == "Load Common Structures" ;
inner join t2 t3 on t1.custno=t3.custno and ;
t3.task == "Load Final Structure" and t3.pctComplete < 100
Cetin