Report wizard not working with Crosstab Queries 1

[bhoran]

I have a crosstab Query with its source from a union query. When I try to use the report wizard the fields to be selected for the report are not available to be selected, therefore I cannot use the Wizard.

Is this an unavoidable issue with Access 2000 or do I need to switch something on or install something extra?

I can still build a report manually but using the wizard would same me some time.

Cheers

 

[hneal98]

Sounds like a pain, but you can create a regular query based on your crosstab and then base your report on that regular query.

 

[bhoran]

No that doesn't work either, but it works if I use it over over a simple query not linked to a crosstab query.

I can create one over the Union query also so it seems it is just the crosstab that it has issues with.

I can also create one over a simple crosstab that only looks over a tbl.

It is a bit annoying. Just wondering if other have the same issue??

 

[dhookom]

bhoran,
Have you specified your Column Headings in this property in the query? Does your crosstab rely on criteria? Maybe you should share the SQL of your crosstab with us.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

The crosstab does rely on crieria from a form, I have defined the parameters of that selection, But I have not defined the parameters of the column heading field.

I can obviously build a report over this query its just that the report wizard shows no fields in the selection area so I have to build the report from scratch.

Here is the SQL.

PARAMETERS [Forms]![RunReports]![cmbRunRptsFY] IEEEDouble, [Forms]![RunReports]![cmbRunRptsYTD] IEEEDouble;
TRANSFORM Sum(RptgByPeriodTotal.AMT) AS SumOfAMT
SELECT RptgByPeriodTotal.PGCODE, RptgByPeriodTotal.PROMGRP, RptgByPeriodTotal.LD, Sum(RptgByPeriodTotal.AMT) AS [Total Of AMT]
FROM RptgByPeriodTotal
GROUP BY RptgByPeriodTotal.PGCODE, RptgByPeriodTotal.PROMGRP, RptgByPeriodTotal.LD
PIVOT RptgByPeriodTotal.PERIOD;

the source of this crosstab is a union query (RptgByPeriodTotal) combining 3 other queries.

Thanks again

 

[dhookom]

[Forms]![RunReports] must be open with legitimate values in the controls. It would also help if you entered the Column Headings (unique PERIOD values).



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

Thanks dhookom, i tried it again with the RunReports form open with valid values, still no luck.

The column heading is the period firld in the design of the quert I am not sure how I go about defining it in the sql??

Cheers
Bhoran

 

[dhookom]

Column Headings is a property of the crosstab query. If you view the properties, you will see this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

Fantastic dhookom that is working fine now. Thanks for your help.