cross tab query (97)

Topic

New Reply

1. I have already created 1 cross tab query and a report that goes with it.
2. I am trying a second cross tab query – it has only one difference from the first one – it prompts the user for input.
3. The query works fine. I put the prompt/parameter in the parameter list of the cross tab query. All is well.
4. Now when I go to create a report based upon this query the problem arises.
5. As I go thru the report wizard, when I select the cross tab query, no fields are listed.
6. This brings everything to a halt.
7. I tried it with no report wizard, just design view.
8. When I do that, the query automatically runs as soon as I select design view, b4 I put any fields on the report. It implements the prompt twice. The result is no report data. It labels the report report1. And then there is no report1 there in the db window.

What’s happening here?

Soc

Reply | Quote

Replies

I found the answer but don’t know why it solved.
I opened the ct query in design view. I clicked in the background and then opened the properties list.
In the column headings row, I listed the column headings.
Everything then worked out to create the report.

Now my question is this:
In the first ct query i did not go to the properties and did not list the column headings in the col. head. row. As a matter of fact , in the 1st one, that row is still blank, but the query and associated report work fine.

Will the absence of this info cause a problem later on?

Soc

Reply | Quote

Sometimes it’s difficult to fiqure out a query by looking at it in design view. If you look at it in sql view, you will see what this query is really doing. You can see alias column names (if any), types of joins, sort order, etc…

Reply | Quote

I looked at the sql but still can’t figure out anything.

As I said, I was able to create the report after I listed the columns in thecolumns heading row of the property sheet of the query.
When i did that and then ran the report wizard, the available fields showed up so I could select them for the report. Before that I could not do that.

Now my question is this: the 1st ct query I made, i did not list the columns in the column heading row of the properties list. I was able to create a report based on the ct query.

Will the fact that I have not listed the columns in the property sheet/col. headings, affect anything later on.?
Why did it work in 1 case and not the other?

Does it have to do with the prompt for criteria in 1 query and not in the other?

Soc

Reply | Quote

I suspect that the one that works doesn’t have any parameters or input criteria in it. If you do have a combination of parameter and crosstab query, you need the column headings so that it can tell what fields are available.

Reply | Quote