Tableau MS_Access 未返回正确计数 [英] Tableau MS_Access Not Returning Correct Count
问题描述
我已将 Tableau 连接到 MS-Access 后端作为我的数据源.
I have connected Tableau to an MS-Access backend as my datasource.
我的一个表包含测试类别 (1-19)，另一个表包含由不同主管完成的实际测试.主管完成的每项测试都会被分配一个类别.我需要显示每个主管在给定月份内完成的每个类别的测试数量.
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.
如果主管完成了某个类别的测试，我可以显示计数，我遇到的问题是当主管没有完成任何测试时，画面根本不显示该类别.我需要画面来显示类别和 0 的计数.
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.
一位主管 4 月份的屏幕截图(未显示所有类别).
Screenshot for one supervisor for the month of April (not all categories are showing).
预期结果- 4 月份一位主管的屏幕截图(显示所有类别).
Desired results-Screenshot for one supervisor for the month of April (with all categories showing).
这是我的数据源
SPARTN_Livefeed 包含测试信息.完成测试的主管、他们进行的测试编号和日期.
SPARTN_Livefeed contains the test information. Supervisor that completed the test, the test number they did and the date.
R_TestCatalog 包含所有测试编号和每个测试所属的类别 ID 的列表.
R_TestCatalog contains a list of all the test numbers and the category id each test belongs to.
R_TestCategory 包含所有类别和属于每个类别的测试编号.
R_TestCategory contains all the categories and the test numbers that belong to each category.
样本数据
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 ...
推荐答案
您需要一个包含类别和主管的所有可能组合的表格.您应该能够通过交叉连接访问来创建它.然后将交叉连接的表(或查询)左连接到您的测试日期.这将为所有类别和主管提供记录，即使没有测试也是如此.
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.
这篇关于Tableau MS_Access 未返回正确计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
