>Hello:
>
>I'm trying to produce a report that pulls information form 3 tablesand i'm having some difficulty. I have a form that allow a user to select a
>particular report from a combo box and print this report based on
>all the data in my select statements(meaning by not selecting any particular person) or by selecting a person from my combo box (which is a list of peoples names that are either a casemang or nddetby). This combo box is filled dynamically in the program.
>
>I had the query working with the below code but realized I need to include the casemang or nddetby in the final cursor and when I try
>to enter it in the code below it doesn't work.set talk on
>select distinct client.sin, allt9client.surname) + " ," + allt(client.firstname) as fullname, client.phone1;
>fromclient;
>where client.sin in;
>(select coun_sum.sin;
>from coun_sum;
>where(!empty(coun_sum.casemang) or;
>!empty(coun_su,.nddetby));
>)into cursor query1
>select distinct client.sin, allt9client.surname) + " ," + allt(client.firstname) as fullname, client.phone1;
>from client;
>where client.sin in;
>(select act_plan.sin;
>from act_plan;
>where !empty(follow_dte);
>)into cursor query2;
>
>select * from subquery1;
>union;
>select * from subquery2;
>into finalcursor
>use in query1
>use in query2
>
>I need to get the coun_sum.casemang and the coun_sum.nddetby from the coun_sum table into one of my cursors and then the final cursor because my final statement needs to be
> - where coun_sum.casemang = thisform.combo2.value or coun_sum.nddetby = thisform.combo2.value
>
>I need this to list all client information based on the users selection of a paritcular casemang or nddetby form the combobox. And as I've stated abocve where can I add into my query (so that It'll work because UNIONS have to be equal). the coun_sum.casemang and coun_sum.nddetby to load properly in my cursor.
>I'd really appreciate any additional assistance you may offer. Thank you.
Sheena,
Isn't it :
select distinct ;
client.sin, ;
allt(client.surname) + " ," + allt(client.firstname) as fullname, ;
client.phone1 ;
from client ;
inner join coun_sum ;
on client.sin = coun_sum.sin ;
inner join act_plan ;
on act_plan.sin = coun_sum.sin ;
where (coun_sum.casemang = thisform.combo2.value or ;
coun_sum.nddetby = thisform.combo2.value) ;
and !empty(act_plan.followdte) ;
into cursor finalcursor
If I could understand well. In case not, to make 2 cursors union compatible you could use virtual fields :
select firstname, lastname, 0 as quantity ;
from table1 ;
union ;
select space(fsize("firstname","table1")) as firstname ;
space(fsize("lastname","table1")) as lastname ;
quantity ;
from table2
Cetin