Crosstab query problems 1

[TechieJr]

Hi All,

I have a subreport based on a two-value crosstab query that is giving me grief. The crosstab query is based on dhookom's faq701-4524. I have modified it and it was working fine. My crosstab query pulls its info from two other "feeder" queries. Both the other queries contain date criteria as part of their data selection. I am using the "DatePart" function to base the feeder queries on a specified quarter.

When I had the quarter criteria hard coded in the two feeder queries, the whole process worked fine. I have now developed a user form which allows the user to choose a quarter and have adjusted the feeder queries accordingly.

Each feeder query works fine by itself. The crosstab query works fine. When I view the subreport by itself, it works fine. When I run the full report I get the following error:

"You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

and the subreport is not visible in the full report.

If memory serves me correctly, I was getting a different error message last week when I worked on it. It was saying that the equation was too complex.

Can anyone give me some hints/help to try and figure this out? I'm soon due to use this to generate another report.

Thanks in advance,
TechieJr.

 

[dhookom]

Most of the time when you want to use a crosstab query as the record source for a report, you must enter the possible column headings in the Column Headings property.

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

 

[TechieJr]

Thanks Duane! That seems to have cleared up the problem.

Why does it seem that it is the little things that bring things to a hault?

TechieJr.

 

[dorling]

i got the same problem what do i put in the column headings in the Column Headings property.

 

[TechieJr]

Hi Dorling,

What I did was to open the query properties page (open query in Design mode > right click on table area at top > Properties), then typed in the names of the columns that would be coming up in the query results. Each name had to be in quotation marks and seperated by a comma.

For instance, my crosstab query extracted the number occurances and attendance figures for three categories of events our office attends. My colums are "Major Event", "Trade Show", and "Other". Those are the headings I put in the column heading property in my query.

HTH,
TechieJr.

 

[dorling]

thanks a lot for that

i know you can do it with column headings but can u do it with row

 

[TechieJr]

The row headings are generated by the queries in association with the table that is used in this crosstab query.

As I mentioned in my original post, I have two queries which are the source of info for my crosstab. Each of the "feeder" queries supplies an aggrigated total (one being the count of event occurances by category, the other being a sum of attendance in each category). These field names become the row headings in my crosstab.

The results of my crosstab become the source of info for a report. In my report I use more vba code to create more user-friendly terms for the end users.

HTH,
TechieJr.

 

[dhookom]

Dorling,
You can get all of the possible values in Row Headings by creating a query that includes all values from the source. THis is done by double-clicking the join line and selected the option to include all the records from the "all values" table.

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