CODEWITHSUNDEEP
sqlms-accessms-access-2007
dedalus_rex
dedalus_rex

Reputation: 469

SELECT FROM a subquery table consisting of a TRANSFORM ... PIVOT table

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?

Upvotes: 1

Views: 2865

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123594

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;

Upvotes: 2

Related Questions