Spotfire Errors Querying Two MS Access Tables

A coworker reached out to me this week with an interesting problem. He was trying to import a data set into Spotfire from MS Access by joining two tables. No matter what we entered, Spotfire errors querying two MS Access tables. Now, just so we are clear, here’s what we were doing.

1. Click on the plus sign to add a table.
2. Select Browse local file.
3. Navigate to MS Access tables.
4. Click the checkbox on the two tables to import.
5. Modify the query to join the two tables. The modified query looked like this.

SELECT 
'tablePRODUCTION'.*, 
'tableHEADER'.* 

FROM 
'tablePRODUCTION' 
inner join 
'tableHEADER' 
on 'tablePRODUCTION'.'WELLID' = 'tableHEADER'.'WELLID' 

Now, my initial efforts yielded an error saying — invalid syntax near FROM clause. In trying to replicate the error for this post, I got a different error shown below — Invalid use of ‘.’, ‘!’, or ‘()’. in query expression.

Now, the solution was pretty simple, and I’m embarrassed I didn’t find it myself. Spotfire didn’t like the two “Select *” statements. To fix it, all we had to do was modify the query to contain a single “Select *”. I put too much weight in the error message, which was telling me the problem was in the FROM clause and in Spotfire’s defaults (checking two tables populates two Select * statements). This is what works.

SELECT *
FROM 'tablePRODUCTION' 
inner join 
'tableHEADER' on 'tablePRODUCTION'.'WELLID' = 'tableHEADER'.'WELLID' 

Now you know how to fix Spotfire errors querying two MS Access tables. And, as a friendly reminder, don’t forget you can also use ODBC connections to connect to MS Access. See links below to a few of my other posts on Spotfire and MS Access.

Spotfire Version

Content created with Spotfire 10.2.

Spotfire Expression Language Series

Check out my series on learning the Spotfire expression language.

1. How to Learn the Spotfire Expression Language
2. How to Use Axis Names in Spotfire Cross Tables
3. Using the Over Keyword to Slice and Dice Calculations
4. Node Navigation in Spotfire
5. Using the Intersect Keyword
6. Using the Then Keyword

Or these other posts on MS Access…

MS Access vs SQL for Spotfire

How to Load MS Access Tables as Quickly as Possible