>A query challenge, hmm? Try this out:
>
>SELECT pz AS allzones FROM yourtable;
> UNION SELECT dz FROM yourtable INTO CURSOR curs1
>SELECT DISTINCT ck, pz FROM yourtable INTO CURSOR curs2
>SELECT DISTINCT ck, dz FROM yourtable INTO CURSOR curs3
>SELECT curs1.allzones,COUNT(curs2.ck),COUNT(curs3.ck) FROM curs1 ;
> LEFT OUTER JOIN curs2 ON curs1.allzones = curs2.pz ;
> LEFT OUTER JOIN curs3 ON curs1.allzones = curs3.dz INTO CURSOR cursout ;
> GROUP BY allzones
>
>I can't try it out because I am STILL stuck in vfp3 and can't use the JOIN syntax. I would have to use UNION SELECT to simulate the outer joins. I could have tried it in Access 97, which does support the JOIN clause (but not FULL OUTER JOIN) but their SQL syntax is a little different. So you might have to play around with the syntax of that last SELECT. If it doesn't work, maybe someone with up-to-date vfp can clean up this solution if necessary. For instance, the need for LEFT OUTER JOIN to account for zones that don't exist in either pz or dz makes me wonder if COUNT() will actually count correctly.

That's similar to how I ended up doing it. I didn't think of putting the 2 counts together. That might streamline it a bit.

Thanks,

-Michelle

(Below is what I did in case you're curious)

* Get pickup zone, drop off zone, and client number for all non-canceled trips in datespan
SELECT cpzone, cdzone, clientkey ;
   FROM Trip_History ;
   INTO CURSOR pdc ;
   WHERE (cStatus <> "CXL") AND BETWEEN(TTOD(tdatetime), pdBegin, pdEnd) 

* Get a list of all zones used in datespan   
SELECT DISTINCT PADR(cpzone,10) AS zone ;
   FROM pdc ;
   INTO CURSOR allzones ;
UNION ;
SELECT DISTINCT PADR(cdzone,10) AS zone ;
   FROM pdc ;
   
* Get all the distinct pickup zone/client keys
SELECT DISTINCT cPzone, clientkey ;
   FROM pdc ;
   INTO CURSOR pzck 

* Get each pickup zone and a count of clients picked up in that zone
SELECT cPzone, COUNT(clientkey) AS NumClts ;
   FROM pzck ;
   GROUP BY cPzone ;
   INTO CURSOR pzcount

* Get all the distinct drop off zone/client keys
SELECT DISTINCT cDzone, clientkey ;
   FROM pdc ;
   INTO CURSOR dzck 

* Get each drop off zone and a count of clients dropped off in that zone
SELECT cdzone, COUNT(clientkey) AS NumClts ;
   FROM dzck ;
   GROUP BY cDzone ;
   INTO CURSOR dzcount

*Gather the above cursors together as zone description, #clients PU, #clients DO
SELECT A.zone, D.cdesc, B.NumClts AS pics, C.NumClts AS drops ;
   FROM allzones A LEFT OUTER JOIN pzcount B ON A.zone = B.cPzone ;
   LEFT OUTER JOIN dzcount C ON A.zone = C.cDzone ;
   LEFT OUTER JOIN zones D ON A.zone = D.ccode ;
   INTO CURSOR zpd