Eric,

I think part of your comment got lost - it returns only one record with ___ ?

The UNION syntax SELECTs everything from the first part plus records from the second part which are not already found in the first part. (ALL keyword includes all records from the second part.)

When you run each SELECT separately, what do you get?


>I Have two tables with the same structure the first one contans archieved records the second one the actual records. When i need to combine the two tables for presenting sales information i use the UNION clause in an SQL-SELECT statement.

SELECT cArtCode, UPPER(cArtGrp) AS cArtgrp, cArtNaam, ; 
    SUM(iAantal*IIF(factuur.lCredNota,-1,1)) AS iTotAant, ; 
    SUM(iAantal*nArtInkPrs*IIF(Factuur.lcrednota,-1,1)) As nTotInk, ; 
    SUM(iAantal*nArtPrs*IIF(factuur.lCredNota,-1,1)) AS nTotVerk ;
FROM FacRegels,Factuur ; 
WHERE Between(Factuur.dFacDatum,dDatVan,dDatTot) ; 
    AND YEAR(facregels.dFacdatum)+FacRegels.iFactuurnr = ; 
        YEAR(Factuur.dFacdatum)+ Factuur.iFactuurnr ; 
UNION ; 
SELECT cArtCode, UPPER(cArtGrp) AS cArtGrp, cArtNaam, ; 
    SUM(iAantal*IIF(fac.lCredNota,-1,1)) AS iTotAant, ; 
    SUM(iAantal*nArtInkPrs*IIF(Fac.lCrednota,-1,1)) As nTotInk, ; 
    SUM(iAantal*nArtPrs*IIF(fac.lCredNota,-1,1)) AS nTotVerk, ; 
    SUM(iAantal*(nartPrs*IIF(facrg.iArttype=1,fac.nKortPerc1/100, ; 
        fac.nKortperc2/100) ) )as nKort ; 
FROM FacRg,Fac ;
WHERE Between(Fac.dFacDatum,dDatVan,dDatTot) ; 
    AND YEAR(facrg.dFacdatum) + Facrg.iFactuurnr = ; 
        YEAR(Fac.dFacdatum) + Fac.iFactuurnr ; 
GROUP BY 1 ; 
ORDER BY 2,1 ;
INTO CURSOR crVerk

>This seems to work ok but when in one of the tables theres nothing found between de Date interval it returns only one record with