We help IT Professionals succeed at work.
Get Started

Dynamically Create Join Statement in MS Access

Data-Man
Data-Man asked
on
621 Views
Last Modified: 2014-03-09
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
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE