>>I have this SQL:
>>
>>

>>SELECT A.zdesc, A.zone, ;
>>       B.cdesc, B.ctype, ;
>>       NVL(B.ncnt,0) AS pccnt, ;
>>       NVL(C.ncnt,0) AS dccnt, ;
>>       NVL(D.ncnt,0) AS ptcnt, ;
>>       NVL(D.ncnt,0) AS dtcnt ;
>>   FROM allzones A, p_ccnt B, d_ccnt C, p_tcnt D, d_tcnt E  ;
>>   INTO CURSOR zsum ;
>>   WHERE ???
>>

>>
>>The goal is to fill in the where, or, preferably, turn it into JOINs.
>>
>>Tables:
>>A - A list of all the zones and descriptions
>>B - PU zone, category, # of distinct clients in each zone/cat
>>C - DO zone, category, # of distinct clients in each zone/cat
>>D - PU zone, category, # of trips in each zone/cat
>>E - DO zone, category, # of trips in each zone/cat
>>
>>I'm trying to get a cursor with all the zones, and for each zone a list of categories, and for each category # clients in pickup zone and dropoff zone, and # trips in each.
>>
>>So I'd have:
>>

>>Zone: A11
>>   Cat      CPU   CDO   TPU   TDO
>>   AMB       1     1     2     2
>>   NAMB      3     1     1     3
>>

>>
>>I'd really appreciate any help. I've been working on it a while and I just can't figure out what to join to what and in what order.
>>
>>Thanks,
>>
>>-Michelle
>
>Some of this stuff looks familiar....:)
>
>Once again, I haven't upgraded from vfp3 yet, so I don't know the exact JOIN syntax (and Access 97 SQL is not strict ANSI SQL). Getting this right requires studying the details of what combinations of outer joins are supported by vfp SQL, whether the order of joins matters, and whether parentheses can be used (they are in ANSI SQL, but not in Access 97 SQL). You will want to use LEFT OUTER JOINs to join allzones A with each of your other tables on allzones.zone = pccnt.DOzone and so on. Your four pick-up - drop-off tables need to be FULL OUTER JOINed to each other on p_ccnt.ctype = d_ccnt.ctype. Putting these outer joins in the WHERE clause (as I would have to do in vfp3) would require a forest of UNION SELECTs.
>
>I remember your previous question which probably resulted in creation of the cursor "allzones". I am supposing that not all categories are guaranteed to appear in any of the tables B - E. I think that the FULL OUTER JOINs will solve that problem if you can stack them properly. If you can't get that to work, it might be easier to replace "allzones" with an "allzonesandcats" cursor created much the same way I think you created allzones, i.e. SELECT DISTINCT DOzone AS zone, ctype AS cat FROM p_ccnt INTO CURSOR zonecats UNION SELECT DISTINCT PUzone, ctype FROM d_ccnt UNION SELECT ... ORDER BY zone,cat. Then you could LEFT OUTER JOIN zonecats to each of the tables B - E on zonecats.zone = B.PUzone AND zonecats.cat = B.ctype and so on.
>
>The heavily numeric databases I have developed were in Access, so I ended up learning Access SQL better than vfp3 SQL. We are about to order vfp6 as soon as the bean counters approve. Then I'll be better at providing complete answers.

***************
Actually, the problem I originally had was finding the number of distinct clients in each PU and DO zone. I managed to do that one. This time, though, I'm throwing categories into the mix.

This is what I start with:

Table Trip_History:
tripkey, clientkey, pzone, dzone, need, purpose, class

And I need to turn it into a report:

Zone
   Needs  CPU CDO TPU TDO
      WC ...
   Purposes
      MED ...
   Classes
      AMB ...
   Total ...

So far I have about 10 cursors. I keep thinking there's got to be a better way, but I don't know it if there is. I managed to gather all the info into the various cursors and then I got stuck.

I think your last suggestion is the best one. I never thought of getting the zones from the count cursors. I was getting them from the original Trip_History. I like the idea of adding the cats right there.

Thanks for the input. I'll let you know if it works.

-Michelle