Link to home
Start Free TrialLog in
Avatar of Data-Man
Data-ManFlag for United States of America

asked on

Dynamically Create Join Statement in MS Access


I have a form that allows the user to export fields from multiple tables within my application.  There are multiple functional areas (tables) and some of the tables have lookup tables. So depending on what fields the user selects there could be multiple joins and some of the lookups are performed at a LEFT join due the fact that the value may be null.

When the user has selected their desired fields, I need to dynamically build the SQL statement.  Creating the SELECT and the WHERE is easy…got that working, no problem.  It’s the JOIN clause that is giving me a headache.  If I was in SQL Server, this wouldn't be a problem.  

However, in MS Access, the joins all seem to be nested.  When I build a simple query like such..

SELECT  table1.a, table1.b, table2.b, table3.key

FROM table1
      INNER JOIN table2 ON table2.key = table1.key
      INNER JOIN table3 ON table3.key = table2.key

MS Access returns an error.  

When I try with two tables it works
SELECT  table1.a, table1.b, table2.b, table3.key

FROM table1
      INNER JOIN table2 ON table2.key = table1.key

When I let Access build the join between the 3 tables the JOIN looks like this…

FROM (table1 INNER JOIN table2 ON table1.key = table2.key) INNER JOIN table3 ON table2.key = table3.key

Notice the addition of the parenthesis

If you have created multi table queries and then looked at the Join clause in SQL View you have probably seen the nesting of the tables that Access creates for the join.

I have tried to change the Access database to be ANSI-92 compliant, but the above query 3 table query still fails.

I need to be able to dynamically create a join statement that Access will understand.  

We are moving to SQL Server later this year and this won’t be an issue, but in the meantime, I need to roll out this functionality.

Any insight would be greatly appreciated.

Avatar of PatHartman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Data-Man


Using the flattened query is my fallback position for the left joins on the major tables. However, it would be nice to be able to create the joins dynamically, as more and more functional areas become available, it would be nice to easily add them to my export tables and have the joins actually work.

I've been working in Access for 20 years and dream in code.  So the quantity of code does not scare more...if I could understand the process behind the Access joins, I could code it.  I'm also open to letting Access build the query using the design grid, provided I could do it behind the scenes, completely transparent to the end user.

Is there something in Jet that I could access?

Access (or, to be more specific, Jet/Ace) is not ANSI SQL compliant.  Yes, you need those parentheses when you join more than two tables, and yes, that's annoying.  You have to account for it in your code.
Yes, but the logic of how to apply the parenthesis especially when working with LEFT joins does not seem to be documented.

If I knew the logic behind the rack and stack of the tables I could duplicate it.

By the way, the setting to make an MS Access database ANSI-92 compatible is under...

File | Options | Object Designers | Query Design | SQL Server Compatible Syntax (ANSI-92)

If you check one of the check boxes, you will get a warning that any existing queries may not return the same results.

Thanks for that, Mike, I never knew that was there!
I recommend a simpler approach to your problem.  Create a joined-table 'view' of the data and have the generated SQL use that stored query in its FROM clause.

Benefits of this approach:
1. You can use the query designer to easily create the query
2. Your user interface should be simpler
3. It should perform faster.
4. Quicker to develop
5. Easier to maintain
6. Extensible -- you and your users can create different views for everyone
Although this wasn't what I was looking for, no one else has provided any details as to my original question. And since my fall back was to use a flattened query for the base tables I chose Pat's answer as the solution.

In my solution I created multiple (5) flattened queries (one for each functional area of data)  and based on the user selection of fields I select the correct join.  I manually created every possible combination of the 5 flattened queries and then chose the applicable join to use with the SELECT and WHERE clauses.  Works like a champ.  :-)

Thanks Pat.

Glad you got it working.