Quick Tip: Using Access crosstab queries in Tableau

Access databases are still commonly used by many businesses. A question I keep hearing is how to use crosstabs created in Access as data sources in Tableau as such queries are not displayed in the list of views & tables in the connection experience, nor can they be used via Custom SQL option in Tableau.

Let’s use the old Coffee Chain sample data source in this example with the following crosstab query and save it with the name “CoffeeChain Query”.

TRANSFORM Sum(factTable.Sales) AS SumOfSales
SELECT Product.[Product Type] AS ProductType
FROM (Product INNER JOIN factTable ON Product.ProductID = factTable.ProductID)
INNER JOIN Location ON factTable.[Area Code] = Location.[Area Code]
GROUP BY Product.[Product Type]
PIVOT Location.Market;

This query makes new columns from the unique values of Market, returning a table in the following form in Access :

Access crosstab query results

Copy-pasting this query into Tableau as Custom SQL will result in errors. Even though it is saved in the Access file, “CoffeeChain Query” won’t display in the list of views/tables either.

Making this table accessible from Tableau takes one little step that involves creating another query on top of this crosstab.

Creating a new Access query/view

You can follow Create > Query (wizard or designer depending on your preference) by doing a SELECT * from the “CoffeeChain Query” listed in Queries tab.

Configuring the new Access query

Let’s save this as “Cross-Tab Query”.

Saving the Access query/view

And now, if you connect to the Access file from Tableau, you will see that Cross-Tab Query is shown in the table list.

Access crosstab is now accessible from Tableau

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s