访问:“交叉表"非数值数据的查询效果 [英] Access: "crosstab" query effect for non-numeric data
问题描述
我正在使用Access 2007，需要帮助来创建查询.如果这太疯狂了，对不起！我认为更多的细节可以更好地理解.我不是程序员，希望别人能帮忙.
I am using Access 2007 and need help creating a query. If this is crazy long, sorry! I figure the more details the better understanding. I am not a programmer--I hope someone will help.
我需要知道如何将非数字数据折叠/汇总/汇总到具有特征/值的单个行中.我想要的是 like 交叉表查询，因为我希望来自该共享/公用值的不同行中的唯一单元格值沿行旋转/显示在同一行的列中.但是，我的数据不是数字的，它需要alpha列标题，交叉表禁止该标题.
I need to know how to collapse/summarize/roll up non-numeric data into a single row that shares a characteristic/value. What I want is like a crosstab query because I want unique cell values from different rows pivoted/displayed in columns along one row for that shared/common value. However, my data isn't numeric and it requires alpha column headings, which crosstabs prohibit.
数据:
- Badge_code 24在3个位置产生:110、210和320(代表芝加哥，罗克福德和圣路易斯)
- Badge_code B9在2个位置:110和280(芝加哥和皮奥里亚)
- 在3个位置的徽章代码C1:200和210(印第安纳波利斯和罗克福德)
每个badge_code-location组合在单独的行中，总共8行. 徽章代码的数量可以/将会增加.
Each badge_code-location combination is in a separate row, 8 rows total. The number of badge codes can/will grow.
我尝试过的查询:
Count(tbl_BadgeType.Badge_type_number) AS CountOfBadge_type_number
SELECT tbl_BadgeType.Badge_code
FROM tbl_BadgeType
GROUP BY tbl_BadgeType.Badge_code
PIVOT tbl_BadgeType.Location_production;
Badge_type_number是用于标识每个徽章代码位置组合的主键.
Badge_type_number is the primary key used to id each badge code-location use combo.
结果:
Badge Code 110 200 210 280 320
24 1 1 1
B9 1 1
C1 1 1
列标题是数字，不能在交叉表中更改为位置名称，我想要的是是"而不是"1". (显然，它们是1，因为它是交叉表中的一个计数函数，我实际上并不需要...)
The column headings are numeric and can't be changed to location names in crosstabs and I want a "Yes" instead of a "1". (Obviously, they're 1s because it's a count function in the crosstab, which I don't really need...)
期望的结果:
Badge Code Chicago Indianapolis Rockford Peoria St. Louis
24 Yes No Yes No Yes
B9 Yes No No Yes No
C1 No Yes Yes No No
问题/问题:
- 从最严格的意义上讲，交叉表查询不是我所需要的相当，但是我不知道如何模拟其汇总"效果.这是6页晦涩难懂的页面和1页有用的清晰内容之间的区别.
- 如果要使用交叉表 ，那么我不知道如何将"1"值显示为是".我在其他查询和报告中使用了IIF语句，将1/0值转换"为是/否"，但无法弄清楚在该查询或其报告中放置IIF的位置.
- A crosstab query in its strictest sense is not quite what I need, but I do not know how to simulate its "roll up" effect. It's the difference between 6 pages of obscurity and 1 page of useful clarity.
- If a crosstab is the way to go, then I can't figure out how to get the "1" values to be displayed as a "Yes". I've used IIF statements in other queries and reports to "translate" 1/0 values to "Yes/No" but can't figure out where to put the IIFs in this query or its report.
这有意义吗?我问得太多了吗?我有希望吗? :^)
Is this making sense? Am I asking too much? Is there hope for me? :^)
推荐答案
哇！我没想到会这么快就得到答案.
Wow! I wasn't expecting an answer so soon.
这是tbl_BadgeTypes相关字段的结构和样本数据:
Here's the structure and sample data of pertinent fields of tbl_BadgeTypes :
Badge_code Badge_description(both are Text fields)
01 Faculty/staff
04 College of Law
62 Student-Peoria
86 Visitor
B7 Police
实际上使用徽章代码的位置在tbl_TokenInstance中(令牌的每个实例的行-徽章，ID等).徽章代码可以在不同的位置使用；它们不是唯一的.是:
The locations where a badge code is actually USED is in tbl_TokenInstance (a row for each instance of a token--badge, ID, etc. A badge code may be used by different locations; they're not exclusive. Pertinent data is:
Instance_number(autonumber) Prod_site_num(Integer) Badge_code(text)
1 110 B7
3 110 B7
4 110 B7
27 310 04
15 210 62
21 110 62
16 110 01
22 210 01
25 310 01
31 110 86
32 120 86
最后，生产站点编号和描述的来源列表在list_ProductionSite中:
Last, the source list of production site numbers and descriptions is in list_ProductionSite:
Production_site_number Production_site_description
110 Chicago IDC
120 Chicago CS
210 Springfield IDC
310 Champaign IDC
320 Champaign CS
这篇关于访问:“交叉表"非数值数据的查询效果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
