CODEWITHSUNDEEP
ms-accesstableau-api
Tom
Tom

Reputation: 77

Tableau MS_Access Not Returning Correct Count

I have connected Tableau to an MS-Access backend as my datasource.

One of my tables has testing categories (1-19) and the other table has the actual tests done by different supervisors. Each test that the supervisor completes is assigned a category. I need to show a count of how many tests in each category each supervisor has completed in a given month.

I am able to show the count if the supervisor completed a test in a category, the problem I am having is when the supervisor has not completed any test, tableau is not showing the category at all. I need tableau to show the category and a count of 0.

It is showing some categories with a zero count, I believe this is because this supervisor has completed a test in this category during a previous month.

I have tried all different type of join with no luck.
I do not believe that access allows a full outer join.

Screenshot for one supervisor for the month of April (not all categories are showing).

enter image description here

Desired results-Screenshot for one supervisor for the month of April (with all categories showing).

enter image description here

Here is my datasource enter image description here

SPARTN_Livefeed contains the test information. Supervisor that completed the test, the test number they did and the date.

R_TestCatalog contains a list of all the test numbers and the category id each test belongs to.

R_TestCategory contains all the categories and the test numbers that belong to each category.

sample data

SPARTN_Livefeed
Sheet_ID  Supervisor_ID   Test Number   Date
OB-111       1111            101          01/01/2020
OB-112       1111            401          03/01/2020
OB-113       1111            201          01/01/2020
OB-114       2222            201          01/01/2020
OB-115       2222            201          01/21/2020


R_TestCatalog
test number        category id
101                    1
201                    2
301                    2
401                    8


R_TestCategory
category id       category description
--------------------------------------
1                 Signals
2                 Restricted Speed
3                 Equip Securement
etc, etc ...

Upvotes: 0

Views: 63

Answers (1)

Andy TAR Sols
Andy TAR Sols

Reputation: 1735

You'll need a table of all possible combinations of Category and Supervisor. You should be able to create this with a cross join in access. Then left join that cross-joined table (or query) to your test date. That will give a record for all categories and supervisors, even when there hasn't been a test.

Upvotes: 2

Related Questions