Avatar of Data-Man
Data-ManFlag for United States of America asked on

Dynamically Create Join Statement in MS Access

Hi,

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.

Thanks,
Mike
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
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?

Thanks,
mike
Patrick Matthews

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.
ASKER
Data-Man

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.

Mike
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Data-Man

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.

Mike
Patrick Matthews

Thanks for that, Mike, I never knew that was there!
aikimark

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Data-Man

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.

Regards,
Mike
PatHartman

Glad you got it working.