>>>>I have three tables. One stores all available 'Width'. One stores all available 'Substance'. One stores the transaction with 'Width', 'Substance', and 'Weight'. How do I generate a crosstab with the following look with 'Weight' as the content?
>>>>
>>>> Subs1 Subs2 ......
>>>>Wdth1
>>>>Wdth2
>>>>..
>>>>..
>>>>..
>>>>
>>>>
>>>>My transaction may not have all the widths and substances. I tried using FULL JOIN but it gives me an extra NULL row and an extra NULL column. Is there a way I can get rid of the NULL row and the extra NULL column. (the NULL row and column arise because the transaction does not have all the available widths and substances)
>>>>
>>>>Any idea?
>>>
>>>Open the program (using MODI COMM (_GENXTAB)). The comments at the beginning of the file have a description of what parameters, their order, and defaults. You don't have to generate a cursor or table with just three columns. This should give you the information you need to solve your problem.
>>>
>>>hth,
>>>
>>>George
>>
>>Sorry, I don't get you. I know the VFPXTAB parameters. I can of course specify whichever field I want. However my problem is because of the data, not because of field in the input table. I cannot find a SQL statement that can JOIN the three tables and yet does not give me any NULL on the row or column field.
>
>After some additional thought, here's how I would do this. First, I'd create a query to retrieve all the possible combinations between the first two tables:
>
>SELECT WIDTHS.WIDTH, SUBS.SUBSTANCE;
> FROM WIDTHS, SUBS
> INTO CURSOR Combo
>
>This gives me all possible widths and substance combinations. Next, I'd join it with the transaction table.
>
>If I wasn't concerned about getting a match for every combination possible combination:
>
>SELECT Combo.Width, Combo.Substance, TRANS.WEIGHT;
> FROM Combo, TRANS;
> WHERE TRANS.WIDTH = Combo.Width AND;
> TRANS.SUBSTANCE = Combo.Substance
>
>If any of the values in the transaction table don't have a corresponding match in both, then the data may be invalid. If you use a FULL OUTER JOIN then invalid values will create both a NULL row and/or column.
>
>hth,
>
>George

Thanks a lot. I didn't know SELECT without JOIN produces all the combination. That is how the question comes about.

Thanks again for your time.