Crosstab Query

[ddtpmyra]

Hi I have below crosstab query inside MS Access. The result is the sum of new msg are group by month. But there are results that rows are null how can I tell my query to display only rows that has both columns are not null? I hope I explain this very well and below are sample of my results and wanted result.

Current Sample Result:
Mailbox

          01/2011    02/2011
001        12        null
002        45        05
003        12        10
004        null      30

Result wanted:

002      45     05
003      12     10

Here's my current code

TRANSFORM Avg(STATISTICS.[New Msg Count]) AS [AvgOfNew Msg Count]
SELECT STATISTICS.Mailbox
FROM STATISTICS
WHERE (((STATISTICS.[New Msg Count])>=1))
GROUP BY STATISTICS.Mailbox, STATISTICS.[New Msg Count]
PIVOT STATISTICS.MontDate;
 

[beacon]

Hi ddtpmyra,

I struggled with this problem when I first started working with Crosstab queries, but found that you have to create a separate SELECT query that includes all the values you want and eliminates the null values. Once that query is created, you would include that query in the Crosstab query.

Hope this helps,
beacon

[ddtpmyra]

Thanks! That was the last I'm thinking of (smile)

[ddtpmyra]

Anybody who can help me to transform my Access Query to MS SQL query?

TRANSFORM Avg(Qry_Statistic.[New Msg Count]) AS [AvgOfNew Msg Count]
SELECT Qry_Statistic.Mailbox, First(Qry_Statistic.[Average Msg Length]) AS [Avg Msg Length], First(Qry_Statistic.[Oldest Msg Date]) AS [Odest Msg], Last(Qry_Statistic.[Folder Msg Count]) AS [Not New Msg], Last(Qry_Statistic.[Total Msg Count]) AS [Total Msg], Last(IIf(STATUS![Last Log On] Is Not Null,STATUS![Last Log On],"NEVER")) AS [Last Log On], Last(STATUS.[Mailbox Comment]) AS [LastOfMailbox Comment]
FROM Qry_Statistic INNER JOIN STATUS ON Qry_Statistic.Mailbox = STATUS.Mailbox
WHERE (((Qry_Statistic.[New Msg Count])<>0 And (Qry_Statistic.[New Msg Count]) Is Not Null))
GROUP BY Qry_Statistic.Mailbox, Qry_Statistic.Mailbox
PIVOT Qry_Statistic.MontDate;
 

[ddtpmyra]

Here's another dilema I'm having (sigh)

If I have a result like this:

          01/2011    02/2011 
001        12        null 
002        03        05 
003        11        03 
004        null      03
 

And I wanted to display the rows result that has > 10 and not null for either coloumns for 01/2011 or 02/2011
Result Desired:

          01/2011    02/2011 
003        11        03 
 

Result Explanation:
003 is the only row to be displayed because Jan has > 10 value which is 11 the rest are either doesn't have >10 value or the value are null

Current Query:

TRANSFORM Avg(dbo_Raw_Statistics.[New Msg Count]) AS [AvgOfNew Msg Count]
SELECT dbo_Raw_Statistics.Mailbox
FROM dbo_Raw_Statistics INNER JOIN dbo_Raw_Status ON dbo_Raw_Statistics.Mailbox = dbo_Raw_Status.Mailbox
WHERE (((dbo_Raw_Statistics.Mailbox)<>"0") AND ((dbo_Raw_Statistics.[New Msg Count]) Is Not Null))
GROUP BY dbo_Raw_Statistics.Mailbox
PIVOT dbo_Raw_Statistics.MonthDate;