使用选定的版权年份范围作为列标题的交叉表查询 [英] Crosstab query with selected range of copyright year as column heading
问题描述
这是与我以前在我的帖子在这里，但存在不同的问题.
This is another question that is related to my previous post at my post here but with different problem.
在上一篇文章中，我询问如何创建一个交叉表查询，该查询将基于版权年份的4年范围输出一列.答案非常好，但现在我面临另一个挑战.
In my previous post, I ask on how I can create a crosstab query that will output a column based on 4 year range of copyright year. The answer was very good but now I am facing another challenge.
为清楚起见，这是表格上的数据:
To be clear here's the data on the table:
ID CallNo CopyrightYear
1 AH 2000
2 AB 2000
3 BC 2001
4 AH 2000
5 ZA 2005
6 BC 2001
7 AP 2003
10 ZA 2006
11 DA 2009
12 DA 2010
13 RA 1999
14 WE 1997
15 HK 1996
16 AG 2011
根据前一篇文章，sql语句应为:
Based on the previous post the sql statement should be this:
TRANSFORM Count(tab1.ID) AS CountOfID
SELECT tab1.CallNo, Count(tab1.ID) AS [Total Of ID]
FROM table1 AS tab1
GROUP BY tab1.CallNo
PIVOT CStr(Int(([CopyrightYear])/5)*5)+' to '+CStr(Int(([CopyrightYear])/5)*5+4);
输出为:
CallNo 1995 to 1999 2000 to 2004 2005 to 2009 2010 to 2014
AB 1
AG 1
AH 2
AP 1
BC 2
DA 1 1
HK 1
RA 1
WE 1
ZA 2
我的问题是如何合并版权年份在1999年以下和2010年以上的所有结果.我想要一个新的列输出，如下所示:
My question is on how can I combine all the result of copyright year that is below 1999 and above 2010. A new column output that I want is like this:
CallNo 1999 below 2000 to 2004 2005 to 2009 2010 above
AB 1
AG 1
AH 2
AP 1
BC 2
DA 1 1
HK 1
RA 1
WE 1
ZA 2
这意味着，如果某个版权年度低于1980年(如1980年)，则该年份将计入以下1999年"列中.与上述2010年相同，如果有版权年份，如2014年，2016年甚至2020年，则该值将计入"2010年以上"列.
This means that if there is a copyright year that is below 1999 like 1980 it will be counted under the column "1999 below". The same with 2010 above, if there is a copyright year like 2014, 2016 or even 2020 the value will be counted on "2010 above" column.
推荐答案
您应该尝试使用为数据透视转换提供的数据进行其他操作，这些数据会更直接但可能会花费更多时间:
You should try something else with the data provided for the pivot transformation that is more straight forward but might take more time:
创建一个这样的表并进行转换:
create a table like that and the do a transformation:
+--------------+--------+------------+
| NUMOFRECORDS | CALLNO | DATERANGE |
+--------------+--------+------------+
| 1 | AB | 2000 2004 |
| 1 | AG | 2010 above |
| 1 | AP | 2000 2004 |
| 1 | DA | 2005 2009 |
| 1 | DA | 2010 above |
| 1 | HK | 1999 Below |
| 1 | RA | 1999 Below |
| 1 | WE | 1999 Below |
| 2 | AH | 2000 2004 |
| 2 | BC | 2000 2004 |
| 2 | ZA | 2005 2009 |
+--------------+--------+------------+
使用像这样的联合查询创建表:
Creating the table using union query like this:
SELECT count(ID) AS NumOfRecords, CallNo, '1999 Below' AS DateRange
FROM table1
WHERE CopyrightYear <= DateValue('1-1-1999')
GROUP BY CallNo;
UNION
SELECT count(ID) as NumOfRecords, CallNo, '2000 2004' as DateRange
FROM table1
WHERE CopyrightYear between DateValue('1-1-2000') and DateValue('1-1-2004')
GROUP BY CallNo
UNION
SELECT count(ID) as NumOfRecords, CallNo, '2005 2009' as DateRange
FROM table1
WHERE CopyrightYear between DateValue('1-1-2005') and DateValue('1-1-2009')
GROUP BY CallNo
UNION
SELECT count(ID) as NumOfRecords, CallNo, '2010 above' as DateRange
FROM table1
WHERE CopyrightYear >= DateValue('1-1-2010')
GROUP BY CallNo
该查询为您使用的交叉表查询如下:
The use that query for you cross tab query like that:
TRANSFORM Sum(Query1.NumOfRecords) AS SumOfNumOfRecords
SELECT Query1.CallNo
FROM Query1
GROUP BY Query1.CallNo
PIVOT Query1.DateRange;
在MS-Access 2010上进行了测试...
Tested on MS-Access 2010...
这篇关于使用选定的版权年份范围作为列标题的交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
