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 :
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.
You can follow Create > Query (wizard or designer depending on your preference) by doing a SELECT * from the “CoffeeChain Query” listed in Queries tab.
Let’s save this as “Cross-Tab Query”.
And now, if you connect to the Access file from Tableau, you will see that Cross-Tab Query is shown in the table list.
