Crosstab query with selected range of copyright year as column heading

Question

This is another question that is related to my previous post at my post here but with different problem.

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

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);

And the output is:

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   

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   

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.

Answer 1

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;

Tested on MS-Access 2010...