Crosstab fields not available to add to Report

[PeakH]

I am trying to create a report with a Crosstab Query as the record source, but when I try to add fields there is a note in the Fields List that says



"No fields available to be added to the current view. The current recordsource may be invalid (for example, it may contain an invalid join expression), or there was some other problem gathering recordsource information for this object."


The query itself works just fine.
When I try adding an unbound field and then binding it to the query in the Property Sheet, the field displays #Name?


SQL for my Query [Q-MRProdData]:

PARAMETERS [Enter Month/Year] Text ( 255 );
TRANSFORM Sum([Q-MRRevGram].Sum) AS SumOfSum
SELECT [Q-MRRevGram].Category
FROM [Q-MRRevGram]
GROUP BY [Q-MRRevGram].Category
PIVOT [Q-MRRevGram].MoName;

This query gets its data from a single Union Query [Q-MRRevGram]

[ranman256]

Because crosstabs can have various changing fields,
You should make a 'report' table that has every possible outcome.
make your crosstab append to this,and then report off of that table.

[PeakH]

I'm having a bit of trouble figuring out the best way to do this. I actually want changing fields.

This is for a monthly report that displays sales data for the last 4 months separated by category. Both the month fields change with every report, but I want it set up so that anyone could simply run the report and get the data for the last four months neatly separated. I tried using 1 main report with 3 sub reports, but if there were no sales within one of the categories for a month, they won't line up correctly.

I'm not sure how I would organize the 'report' table to account for the changing months.

[aytee111]

Have a routine in VBA which has the crosstab query as a recordset and append to a reports table. If there are always 4 month fields then you can put them into the month1 to month4 fields on the record.

Have a form with a button to produce the report and a textbox where the user can enter the month/year.