Return all row headings in crosstab query

S

spsquared

Hi,

I am trying to write a query to do the following. I have data that
looks like this.

Record_Number Status Created_on
2001 NOPO pMDO 1/1/08
2002 NOCO MDRO DCMT 1/5/08
2003 NOPR PMNT 2/1/08
2004 NOPO MDRC 1/10/08

When I run this SQL, I get this result.

SQL

TRANSFORM Count(Table1.Record_Number) AS CountOfRecord_Number
SELECT IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null))) AS Type
FROM Table1
GROUP BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
ORDER BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
PIVOT Month([Table1]![Created_on]) & "/1/" & Year([Table1]!
[Created_on]);

Result

Type 1/1/2008 2/1/2008
1PMDR 1
2MDR 2
3PMNT 1

I want all 3 row values to be displayed at all times. When I filter
this query for Create dates between 1/1/08 and 1/31/08, I lose the
last row.

SQL

TRANSFORM Count(Table1.Record_Number) AS CountOfRecord_Number
SELECT IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null))) AS Type
FROM Table1
WHERE (((Table1.Created_on) Between #1/1/2008# And #1/31/2008#))
GROUP BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
ORDER BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
PIVOT Month([Table1]![Created_on]) & "/1/" & Year([Table1]!
[Created_on]);


Result

Type 1/1/2008
1PMDR 1
2MDR 2

The result that I need is

Type 1/1/2008
1PMDR 1
2MDR 2
3PMNT 0

Any help would be awesome.

Thanks.
 
R

Rob Parker

You have to force the zeros to appear:
TRANSFORM Nz(Count(Table1.Record_Number),0) AS CountOfRecord_Number
...

HTH,

Rob
 
S

spsquared

Rob,

Thanks for the reply.

I tried this without the filter on Create Date

TRANSFORM Nz(Count(Table1.Record_Number),0) AS CountOfRecord_Number
SELECT IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null))) AS Type
FROM Table1
GROUP BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
ORDER BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
PIVOT Month([Table1]![Created_on]) & "/1/" & Year([Table1]!
[Created_on]);


Then I created a new query from that query and added my filter on
Create date. It gave me exactly what I wanted. Thanks so much.
 
G

George Nicholson

That isn't his real issue. His real problem is that he needs 3PMNT to appear
even if there are no records using that Status within his date range.

Create a StatusList query that has one unique record for each of the Status
values you want to see. Then create another query that uses a
Outer/left/Right join to connect the StatusList with your crosstab. This
will force all StatusList values to appear, along with any matching values
in your crosstab. Blank vs zero might still be an issue, but you should be
able to use nz() to fix that on a report if really necessary.

--
HTH,
George


Rob Parker said:
You have to force the zeros to appear:
TRANSFORM Nz(Count(Table1.Record_Number),0) AS CountOfRecord_Number
...

HTH,

Rob

Hi,

I am trying to write a query to do the following. I have data that
looks like this.

Record_Number Status Created_on
2001 NOPO pMDO 1/1/08
2002 NOCO MDRO DCMT 1/5/08
2003 NOPR PMNT 2/1/08
2004 NOPO MDRC 1/10/08

When I run this SQL, I get this result.

SQL

TRANSFORM Count(Table1.Record_Number) AS CountOfRecord_Number
SELECT IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null))) AS Type
FROM Table1
GROUP BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
ORDER BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
PIVOT Month([Table1]![Created_on]) & "/1/" & Year([Table1]!
[Created_on]);

Result

Type 1/1/2008 2/1/2008
1PMDR 1
2MDR 2
3PMNT 1

I want all 3 row values to be displayed at all times. When I filter
this query for Create dates between 1/1/08 and 1/31/08, I lose the
last row.

SQL

TRANSFORM Count(Table1.Record_Number) AS CountOfRecord_Number
SELECT IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null))) AS Type
FROM Table1
WHERE (((Table1.Created_on) Between #1/1/2008# And #1/31/2008#))
GROUP BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
ORDER BY IIf([Table1]![Status] Like "*pmd*","1PMDR",IIf([Table1]!
[Status] Like "*PMNT*","3PMNT",IIf([Table1]![Status] Like
"*MDR*","2MDR",Null)))
PIVOT Month([Table1]![Created_on]) & "/1/" & Year([Table1]!
[Created_on]);


Result

Type 1/1/2008
1PMDR 1
2MDR 2

The result that I need is

Type 1/1/2008
1PMDR 1
2MDR 2
3PMNT 0

Any help would be awesome.

Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter in Crosstab Query (?) 6
Crosstab Qry, column heading, by week number, show "Mar 9-16" 3
Crosstab query for the report 2
How to avoid duplicates in an union query 9
Top 10 in Crosstab query 3
Reverse Axis in Crosstab Query 3
Values in crosstab represent change -- how to get query to return"current value" for each month? 1
Row Headings in a Crosstab Query 2

Top