>What is IIRC?
IIRC - If I Rememeber Correctly
>I can't move this app into VFP8 yet.
>
>I tried sending the commands one at a time but I still didn't get data back when I executed the 3rd command which joins the two temporary tables so I thought maybe there was an issue with access to the temp tables and sending the command all at once might solve it.
>
>The issue is I need to get a distinct version of the two fields from the INCOME table. Initially I only had one select statement I was executing and I did a left outer join into the INCOME table on the query statement. Then I found that the INCOME table is dirty, having as many as four copies of each row, so my quantites would double, triple, etc. because of the duplicate rows. The INCOME table is imported from another system and there are no controls on it so even if it were cleaned up now I am sure it will have duplicates again in the future.
You can used detrived tables in SQL server as shown below
select dt2.*, dt1.recdate
from (
select distinct purord, recdate from income
) dt1
left join (
select sc.repcode, sc.purord, count(sc.sernum) as qty , rc.stuff
from scraps sc
LEFT OUTER JOIN redcode rc
ON sc.scrapcode = rc.number
where sc.purord in (select distinct purord from income
where custnum = 'AF' OR custnum = 'AF3')
and sc.actiondate >= '01/01/05' and sc.actiondate <= '09/07/05'
and rc.stuff not like 'INPUT ERROR%'
group by sc.repcode, sc.purord,
sc.scrapcode, rc.stuff
order by sc.repcode, sc.purord, sc.scrapcode, rc.stuff
) dt2
on dt1.purord = dt2.purord
order by dt1.purord
Also it's a bad idea to use one letter meaningless table aliases because it makes code unreadable.
--sb--