Joining tables in Access

[Samuel]

Hi

I am looking for the definition of how to join multiple tables in queries. I
normally use SQL server and the access queries seem very complicated

Thanks,
Samuel

 

[Guest]

Sam,

If you are used to writing your queries in SQL Server, just use the Access
SQL view to enter your queries. You don't need to wrap all of the joins in
paranthesis, Access will do that for you.

Or use the query grid just like you would in SQL Server (although the
columns in the SQL Server grid are layed out vertically and they are layed
out horizontally in Access).

 

[Samuel]

Thank you for your reply,

I actually use my own SQL statement generator (it is a VB script). It is
straight forward for SQL Server but not for access as the user of the SQL
Generator may add many table with different type of joins

Samuel

 

[John Spencer]

Dale,
Could you explain further.

I've never seen Access add the parentheses is SQL View.

As a matter of fact if I don't add the parentheses in the FROM clause of a
multi-table (3 or more) query, the query fails with a syntax error.

I know that Access will add unneeded parentheses in the WHERE clause and
other places, but it requires that the parens be there in the JOIN clause
and you must add them in the SQL view if you construct the SQL there.

This errors no matter what I do
SELECT *
FROM FAQ INNER JOIN FAQ1 ON FAQ.fID = FAQ1.fID
INNER JOIN FAQ2 ON FAQ1.fID = FAQ2.fID;

This works
SELECT *
FROM (FAQ INNER JOIN FAQ1 ON FAQ.fID = FAQ1.fID)
INNER JOIN FAQ2 ON FAQ1.fID = FAQ2.fID;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dale Fye]

My mistake.

I was at Advisor DevCon last Fall and could swear that one of the speakers
indicated that in Access 2003 JET would take care of that for you. Any
chance they were talking about Acces 2007? I've only recently migrated to
2003 (the office finally upgraded and I got a home use program copy).

Dale