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. In my previous post, I ask on how I can create a crosstab query that will o

Solution 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:

SELECTcount(ID) AS NumOfRecords, CallNo, '1999 Below'AS DateRange
FROM table1
WHERE CopyrightYear <= DateValue('1-1-1999')
GROUPBY CallNo;

UNIONSELECTcount(ID) as NumOfRecords, CallNo, '2000 2004'as DateRange
FROM table1
WHERE CopyrightYear between DateValue('1-1-2000') and DateValue('1-1-2004')
GROUPBY CallNo

UNIONSELECTcount(ID) as NumOfRecords, CallNo, '2005 2009'as DateRange 
FROM table1
WHERE CopyrightYear between DateValue('1-1-2005') and DateValue('1-1-2009')
GROUPBY CallNo

UNIONSELECTcount(ID) as NumOfRecords, CallNo, '2010 above'as DateRange
FROM table1
WHERE CopyrightYear >= DateValue('1-1-2010')
GROUPBY CallNo

The use that query for you cross tab query like that:

TRANSFORM Sum(Query1.NumOfRecords) AS SumOfNumOfRecords
SELECT Query1.CallNo
FROM Query1
GROUPBY Query1.CallNo
PIVOT Query1.DateRange;

Tested on MS-Access 2010...