I don't know if you've tried this, but don't use one SELECT statement. Try breaking things up into multiple SELECTs. VFP will only join two tables at a time anyway.

>I have a set of related tables: Customer Accounts(Acct), real estate Parcel(real), Personal Propertry (Pers) and Flat Charges(flat). Real, Pers and Flat are always tied to one Acct record. In addition, tables: Real, Pers and Flat may also be related between themselvs by a parcel id code. If I build data files as diagrammed below (a test case), I need an SQL Select that would build and join records the same way:
>
> Acct Real Pers Flat
> acct|parcel acct|parcel acct|parcel
>(1) aaaa aaaa|1111 aaaa|1111 aaaa|1111
>(2) aaaa aaaa|2222 aaaa|2222 no data
>(3) aaaa aaaa|3333 no data aaaa|3333
>(4) aaaa no data aaaa|4444 aaaa|4444
>(5) aaaa aaaa|5555 no data no data
>(6) aaaa aaaa|no data aaaa|6666 no data
>(7) aaaa no data no data aaaa|7777
>
>
>"aaaa" represents a particular account, "1111", "2222", and so on represent parcel Id codes. "aaaa|1111", and so on, represent the fields (Acct and Parcel) in each file that relate to the other files.
>
>With the data contained in the files exactly as displayed above, my SQL selects have not been able to build result sets that produce a like display. For example,when I sucessfully join (acct=real=pers=flat) (row (1)), the join result for row (2) (acct=real=per) also selects row (1) again because it satisfies the where/join condition also. I have tried unions, various joins, where clause joins, etc. and connot get the desired results. I have created a very slow solution that does work by processing one account and parcel number combination at a time. This takes 4 hours to process. I need this data retreived for many different requirements from inquiry to bill calculations.
>
>I've purchase SQL books and tried hundreds of experimentation/variations. As I said before, If you can help me out with a solution, you are a better man/woman than I, as Im out of ideas.
>
>Thanks Gary.