Need to create a report using a crosstab query

[mjps]

I'm new on the boards ... I'm trying to create a reporting using the wizard
from a crosstab query with the following SQL information:

PARAMETERS [Forms]![frm_stmt]![stmt] Text ( 255 );
TRANSFORM Sum([TOTAL ANNUAL].[2005YE]) AS SumOfDec 05
SELECT tbl_Statement.STMT, [TOTAL ANNUAL].Item
FROM ([TOTAL ANNUAL] INNER JOIN [Items - Order] ON [TOTAL ANNUAL].Item =
[Items - Order].Item) INNER JOIN tbl_Statement ON ([TOTAL ANNUAL].STMT =
tbl_Statement.STMT_short) AND ([Items - Order].STMT = tbl_Statement.STMT)
WHERE (((tbl_Statement.STMT)=[Forms]![frm_stmt]![stmt]))
GROUP BY tbl_Statement.STMT, [Items - Order].Order, [TOTAL ANNUAL].Item
ORDER BY tbl_Statement.STMT, [Items - Order].Order
PIVOT [TOTAL ANNUAL].CO;

The problem is, none of the fields are available for me me to select to
create the report ... it's blank. When I try to create a report using design
view, the pop-up box for parameter I set keeps coming up everytime I try to
move a field onto the report.

 

[Allen Browne]

In query design view, open the Properties box.
Beside the Column Headings property, list all the valid values that could
appear in the Column Headings field.
Save the query.

Since these headings are the field names, the query will not generate all
the fields, even if there is no data for some of them. And since the query
knows what the fields are without having to run all the data, the report
will also know what fields the query will provide.

 

[mjps]

Thank you, Allen! That worked perfectly!

In query design view, open the Properties box.
Beside the Column Headings property, list all the valid values that could
appear in the Column Headings field.
Save the query.

Since these headings are the field names, the query will not generate all
the fields, even if there is no data for some of them. And since the query
knows what the fields are without having to run all the data, the report
will also know what fields the query will provide.

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

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

I'm new on the boards ... I'm trying to create a reporting using the
wizard
from a crosstab query with the following SQL information:

PARAMETERS [Forms]![frm_stmt]![stmt] Text ( 255 );
TRANSFORM Sum([TOTAL ANNUAL].[2005YE]) AS SumOfDec 05
SELECT tbl_Statement.STMT, [TOTAL ANNUAL].Item
FROM ([TOTAL ANNUAL] INNER JOIN [Items - Order] ON [TOTAL ANNUAL].Item =
[Items - Order].Item) INNER JOIN tbl_Statement ON ([TOTAL ANNUAL].STMT =
tbl_Statement.STMT_short) AND ([Items - Order].STMT = tbl_Statement.STMT)
WHERE (((tbl_Statement.STMT)=[Forms]![frm_stmt]![stmt]))
GROUP BY tbl_Statement.STMT, [Items - Order].Order, [TOTAL ANNUAL].Item
ORDER BY tbl_Statement.STMT, [Items - Order].Order
PIVOT [TOTAL ANNUAL].CO;

The problem is, none of the fields are available for me me to select to
create the report ... it's blank. When I try to create a report using
design
view, the pop-up box for parameter I set keeps coming up everytime I try
to
move a field onto the report.