Multiple joins

[BittenApple]

Hello team,
I am totally confused and wreck'd.

I inherited a database from someone in our team, the database has multiple tables that are used in a query at the final step.

This is SQL:

Select ..... into AFinalTable
From table1 Right Join
(table2 Right Join
(table3 Right join
(table4 INNER JOIN
( table5 INNER JOIN
(table6 INNER Join table7)
ON table5.ID=table7.ID)
ON table4.ID=table7.ID)
ON table3.CATID=table4.CATID)
ON table2.ID=table7.ID)
ON table1.ID=table7.ID)

-What makes me confused is that tables are joined and then the join field is written at the end. I have learned after each join, bring the fields that make the join happen. Can we do multiple joins such as top? Is this correct? Is this a good practice? Please advise me.

Thanks,
Bit

[HansV]

The parentheses structure the joins. The easiest way to construct the joins correctly is in Design View. Access will automatically create the SQL corresponding to the join lines that you draw between the tables.

[BittenApple]

Hello Hans,
Thanks for the response that enlightens my life and provide answers to so many of my questions.
When we have 3 left or right join, that can't be managed in design view, we have to write sql for it.
Does really the order of joins matter in inner join?
I am going to turn this query to left query, I have hard time. But I will manage it eventually.
Can we join all the tables and at the bring the join fields? Is this a good method?
Regards,
Bit

[HansV]

You can change an inner join to a left join or a right join in Design View, by double-clicking the join line and selecting the second or third option, depending on your preference.

[BittenApple]

Hans,
That works if I have multiple inner join along with one left or right join; as soon as the number of left or right join increases more than 1, a message box pops up and says that ambiguous join and that sounds correct because Access doesn't know which join to do first, however I will test it.
Regards,
Bit

[HansV]

You have to create them in a logical order, otherwise the query won't work.

[BittenApple]

Hans,
It worked for me.

I wrote SQL for inner join only and I mimicked exactly what I had, it worked. I was wrong on what I thought that join fields are not placed in proper place, actually they are absolutely in right place; (Parentheses structure joins).

Then for all the tables, I changed the Right join to left and I got the same result. I have been thinking about these joins since yesterday evening.

Thank you for all your help.
Regards,
Bit