Solved

Dynamically Create Join Statement in MS Access

Posted on 2014-03-07
9
564 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
0
Comment
Question by:Data-Man
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39914006
You seem to be attempting to replace functionality that already exists in the Access GUI and without a lot of code, you are going to come up short.  Think about how much code lives behind the QBE.  If you are not prepared to reproduce it, then you need to adjust your sights.

I build standard queries that denormalize lookups that can be used for this purpose.  That should limit or even eliminate the joins you need to create.  So rather than picking columns from a table to export, let the users pick columns from a flattened query.

I also tend to use fixed queries and just let the user add selection criteria.  That is somewhat easier to manage.
0
 
LVL 18

Author Comment

by:Data-Man
ID: 39914018
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39914046
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.
0
 
LVL 18

Author Comment

by:Data-Man
ID: 39914541
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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 18

Author Comment

by:Data-Man
ID: 39914585
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39915100
Thanks for that, Mike, I never knew that was there!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39915132
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
0
 
LVL 18

Author Closing Comment

by:Data-Man
ID: 39916098
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39916388
Glad you got it working.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now