question about complex joins

[Megatron-Lives]

the join syntax for access is starting to irk me...

FROM table1
INNER JOIN
(table2 INNER JOIN
(table3 INNER JOIN
(table4)
ON table3.field1 = table4.field1)
ON table2.field1 = table3.field1)
ON table1.field1 = table2.field1

You are always linking the second to last table you joined to the last table you joined.

So, what happens when table1 is my main table, and tables 2, 3, and 4 are just lookup tables, so there is no shared field between tables 2 and 3 or between tables 3 and 4? The only shared fields are between tables 1 and 2, between tables 1 and 3, and between tables 1 and 4.

You can't repeat a table in the join, so how do you join multiple lookup tables to a single main table?

It's **** easy in SQL Server, you can join a field from any table in the join to a matching field in any other table in the join.

FROM table1
INNER JOIN table2
ON table1.field1 = table2.field1
INNER JOIN table3
ON table1.field1 = table3.field1
INNER JOIN table4
ON table1.field1 = table4.field1

I can't figure out how to do it in Access, though...

 

[Megatron-Lives]

hmmm, apparently that basic syntax goes right out the window with more complex joins

I'm just gonna use the wizard a few times on various test db's until I learn the stupid nesting. What's up with the nesting anyway? Isn't access supposed to be an EASIER version of SQL Server? It shouldn't be twice as hard to write a query...

this is access 2000, btw

 

[Megatron-Lives]

ok, now i see where the "easy" comes in

i don't write my own queries anymore, i just let the wizard write it for me and then copy/paste it to my asp.