I have the following functioning query to create a crosstab/pivot table in Access
TRANSFORM Sum(y.TNAV) AS TNAV
SELECT y.RecDate
FROM BNYDaily AS y
WHERE (((y.AccName) In ("A","B")) AND y.RecDate >= DateValue("1/1/2013"))
GROUP BY y.RecDate
PIVOT y.AccName; )
The problem is that the query returns results with NULL
fields that messes up my calculation. I want to omit rows in this crosstab table that have NULL
value in either columns:
RecDate A B
....
1/25/2013 1,469,004,032.00 968.63
1/26/2013 1,466,082,304.00
1/28/2013 973.91
1/29/2013 1,471,277,440.00 971.66
...
I tried the following query that uses the above query as a subquery without any luck:
SELECT * FROM
(
TRANSFORM Sum(y.TNAV) AS TNAV
SELECT y.RecDate
FROM BNYDaily AS y
WHERE (((y.AccName) In ("A","B")) AND y.RecDate >= DateValue("1/1/2013"))
GROUP BY y.RecDate
PIVOT y.AccName;
) AS t
WHERE t.A IS NOT NULL AND t.B is NOT NULL
which oddly doesn't run in Access and returns an error. If I query from the crosstab query as a saved query table it works. Any ideas?
Instead of "squeezing out" the rows containing Nulls from the results of the crosstab, how about eliminating the rows that produce the Nulls from the source of the crosstab? I just tried the following and it seems to work:
TRANSFORM Sum(y.TNAV) AS TNAV
SELECT y.RecDate
FROM
(
SELECT RecDate, AccName, TNAV
FROM BNYDaily
WHERE RecDate IN (SELECT RecDate FROM BNYDaily WHERE AccName = "A")
AND RecDate IN (SELECT RecDate FROM BNYDaily WHERE AccName = "B")
) AS y
WHERE (((y.AccName) In ("A","B")) AND y.RecDate >= DateValue("1/1/2013"))
GROUP BY y.RecDate
PIVOT y.AccName;