>Tried the following query in VFP9, VFP8, and VFP7. Records returned were 226, 236, and 232 respectively. I was expecting 263 because that is the number of records in the table on the left, RoseBran. Can anyone see what I'm missing here?
>
>SELECT RoseBran.ItemNo, ;
> RoseBran.Descrip, ;
> RoseBran.Descrip1, ;
> SUM(ARSTrans.Shipped) as Shipped ;
> FROM RoseBran ;
> LEFT OUTER JOIN ARSTrans ;
> ON RoseBran.ItemNo = ARSTrans.ItemNo ;
> WHERE BETWEEN(ARSTrans.DateShip,CTOD('2005/01/01'),DATE()) ;
> GROUP BY 1, 2, 3 ;
> ORDER BY 1 ;
> INTO CURSOR TEMP
>
>TIA,
You couldn't expect 263 records because if RoseBran has more then one record with same ItemNo, Descript and Descript1 SELECT will group them to one. Also in that SELECT the records in RoseBran which haven't relative ones in ARSTran or the DateShip in ARSTran aren't in that range you use in WHERE clause also couldn't be included.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.