Remove Nulls To Move Values Up Columns

[duluter]

Hi.

I implemented a query very similar to one found in O'Reilly's SQL Cookbook on page 367 ("Pivoting a result set into multiple rows"). Part of the query uses aggregate functions (MAX, in this case, though the exact function used is arbitrary) to remove null values from within columns to bump the values in those columns "up" the recordset.

Here is my query:

SELECT
BusinessName,
ContractRenewalYear,
Farm,
rnk,
MAX(IIf(CropYear=2008, PackageAndTasks, Null)) AS Year1PAndT,
MAX(IIf(CropYear=2007, PackageAndTasks, Null)) AS Year2PAndT,
MAX(IIf(CropYear=2006, PackageAndTasks, Null)) AS Year3PAndT,
MAX(IIf(CropYear=2005, PackageAndTasks, Null)) AS Year4PAndT
FROM (SELECT e.BusinessName, e.ContractRenewalYear, e.Farm,
e.CropYear, e.PackageAndTasks, (SELECT COUNT(*) FROM
qryContractRenewal1 AS d WHERE e.CropYear = d.CropYear AND
e.WorkOrderNumber < d.WorkOrderNumber) AS rnk FROM
qryContractRenewal1 AS e) AS x
GROUP BY BusinessName, ContractRenewalYear, Farm, rnk;


My output looks like this:

BusinessName__CRYear__Farm__rnk__Year1P&T__Year2P&T__Year3P&T

Test Farms____2009____Farm1__0____AAA______NULL______CCC
Test Farms____2009____Farm2__0____NULL_____DDD_______NULL
Test Farms____2009____Farm2__1____EEE______NULL______FFF
Test Farms____2009____Farm2__2____GGG______NULL______NULL


I had anticipated that for each Business, CRYear, Farm, rnk grouping, all of the values in Year1P&T, Year2P&T, and Year3P&T would get bumped up, eliminating the NULL values in those columns, like this:


BusinessName__CRYear__Farm__rnk__Year1P&T__Year2P&T__Year3P&T

Test Farms____2009____Farm1__0____AAA______NULL______CCC
Test Farms____2009____Farm2__0____EEE______DDD_______FFF
Test Farms____2009____Farm2__1____GGG______NULL______NULL



Note that the scalar subquery above is primarily used to create a "ranking" for each cropyear/workorder combo (rnk). I think it's enough to say that my rankings are working properly. Notice in my first result set above that Farm2 is represented by three rows, with the rankings of 0, 1, and 2. These rankings merely serve to uniquely identify the rows for that farm. Using MAX on the last three columns and grouping by rnk was supposed to eliminate my nulls, like what is shown in the second recordset.

Anyone know why my nulls aren't being eliminated?


Duluter