Subreport error when getting info from XTab query (Access 2000)

Topic

New Reply

Ok, so I have two tables, one for permanent employees, and one for temporary employees. I use a union select query to join the two tables to compile the information needed for my crosstab query. I use this crosstab query to populate a subreport. When I try to view the report and subreport, I get the 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.”

I don’t know where or how to set this property.

Reply | Quote

Replies

See the databases attached to the replies to your previous thread.
Open the crosstab query in design view.
Click in an empty part of the upper half of the query window.
Activate the Properties window (View | Properties if it isn’t visible)
Enter a comma-separated list of column names in the Column Headings property.

Reply | Quote

Somehow I overlook the little things, like the type of query I’m looking at……a select query doesn’t have a Column Headings property.

Now, I have the problem that my subform will not display itself on the same page as my report. I have my report designed to have each location on a separate page, with every company listed below the location….however, my report goes something like:

Page 1 – Location 1, Permanent Employees (mainreport)
Page 2 – Location 1, Temporary Employees (subreport)

I inserted my subreport in the detail section of my main report. I created my report based of a permanent personnel query, copied and pasted it, and changed the source query to an identical temporary personnel query as the source for my subreport. I guess I’m just not sure how to format the report. If my thoughts seem discombobulated, it’s because they are….I’m not really sure what I’m doing wrong or where I should go with this.

Reply | Quote

If you have created a union query that combines permanent and temporary employees, as stated in the first post in this thread, I don’t understand why you have a main report for permanent employees and a subreport for temporary employees. In fact, it’s not clear to me at all what the subreport is for, and how it relates to the main report. Perhaps things will become clearer if you try to explain the structure of the report.

Reply | Quote

I have two reports because I have two different queries (one for permanent, one for temporary). If I can source both queries without using a subreport, please enlighten me.

Reply | Quote

What then did you mean by “I use a union select query to join the two tables to compile the information needed for my crosstab query.”?

Reply | Quote

Are you sure there is room for both the main report data and the subreport on the same page?

If the subreport won’t completely fit on the page, it moves the whole thing to the next page.

You can decrease the page margins (I usually put them down to 10 0r 12 mm) and try to tighten up the subreport by making each section as small as possible.

Another question . Why have two separate employee tables? Why not just one table with a field that indicates whether they are permanent or temporary?

Reply | Quote