>>>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