set report fields

[Guest]

I have a report from a crosstab in which I have month's accross the top, and
percentages by employee as rows. Here is the SQL of the query:

TRANSFORM Avg([PRODUCTIVE_TIME]/[STAFF_TIME]) AS Expr1
SELECT MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
FROM MastrPhoneStatsTbl
WHERE (((MastrPhoneStatsTbl.TEAM_NAME) Like "CC TEAM*"))
GROUP BY MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
PIVOT Month(MastrPhoneStatsTbl!DATE);

I am pivoting by Month, so the data looks like this

1 2 3 4
John Smith 90% 80% 90% 91%

My problem is I want all of the months of the year to show up in the report
even if there is no data for them yet (so I don't have to keep manually
adding fields in the report). Is there a way to do this?

Thanks!

 

[Allen Browne]

Open your crosstab query in design view.
Open the Properties box (View menu).
Enter all the valid values beside the Column Headings property:
1, 2, 3, ... 12

 

[John Spencer]

Try adding the known column values to the query

TRANSFORM Avg([PRODUCTIVE_TIME]/[STAFF_TIME]) AS Expr1
SELECT MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
FROM MastrPhoneStatsTbl
WHERE (((MastrPhoneStatsTbl.TEAM_NAME) Like "CC TEAM*"))
GROUP BY MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
PIVOT Month(MastrPhoneStatsTbl!DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)

 

[Guest]

Wow, that was easy. Thanks!!!

Open your crosstab query in design view.
Open the Properties box (View menu).
Enter all the valid values beside the Column Headings property:
1, 2, 3, ... 12

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report from a crosstab in which I have month's accross the top,
and
percentages by employee as rows. Here is the SQL of the query:

TRANSFORM Avg([PRODUCTIVE_TIME]/[STAFF_TIME]) AS Expr1
SELECT MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
FROM MastrPhoneStatsTbl
WHERE (((MastrPhoneStatsTbl.TEAM_NAME) Like "CC TEAM*"))
GROUP BY MastrPhoneStatsTbl.USERID, MastrPhoneStatsTbl.LASTNAME,
MastrPhoneStatsTbl.FIRSTNAME, MastrPhoneStatsTbl.TEAM_NAME
PIVOT Month(MastrPhoneStatsTbl!DATE);

I am pivoting by Month, so the data looks like this

1 2 3 4
John Smith 90% 80% 90% 91%

My problem is I want all of the months of the year to show up in the
report
even if there is no data for them yet (so I don't have to keep manually
adding fields in the report). Is there a way to do this?

Thanks!