Using VFP 6.0 with Service Pack 3, attempting to create a Local View with more than two tables. The details:

Transaction File (customer transactions):
Desc_fk
Cust_fk
Comp_fk

Description File (Description of transaction codes):
desc_pk

Customer File:
cust_pk

Company File:
comp_pk

The transaction file is related to the description, customer and company files via the foreign keys in the transaction file.

The view creates an SQL select as follows:

select transaction.amount, description.description, customer.name,;
company.name;
from transaction;
inner join description;
inner join customer;
inner join company;
on transaction.comp_fk = company.comp_pk;
on transaction.cust_fk = customer.cust_pk;
on transaction.desc_fk = description.desc_pk

The problem is that this does not work. The transactions selected all appear to belong to the same customer and company which, of course, they do not.

If the SQL command is changed as follows and placed in a prg, it works fine:

select transaction.amount, description.description, customer.name,;
company.name;
from transaction;
inner join description;
on transaction.desc_fk = description.desc_pk;
inner join customer;
on transaction.cust_fk = customer.cust_pk;
inner join company;
on transaction.comp_fk = company.comp_pk

Therefore, it would appear that the view is creating an incorrect SQL statement when more than two tables are specified.

I then coded the correct SQL statement and used the Create SQL View command to create the view in the database. Amazingly, the resulting view changed the SQL statement to the way described above (the one that doesn't work)!

Does anyone understand what is going on and assist me in getting this to work?

Thanks,

Robert