Report based on crosstab query does not open up

[paramit81]

Hi all,
I have a crosstab query based on 2 tables, 1 of which has around 13000 records. When I click on the query, the results are displayed pretty fast. I also have a select query based on the crosstab query so that NULL values wont have any problem in the report. So I try to generate a report based on this select query and when I try to save it, my computer just hangs up. The MS ACCESS just sits there not doing anything.
I need this data very urgently. Is there any other way I can create a report on it or if anyone can solve my problem I wud really appreciate that.

 

[dhookom]

Have you entered the Column Headings values into the crosstab query?

Why do you need a second query? Can't you get rid of the Nulls in the crosstab?

How about sharing some sql views?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[paramit81]

Hi Dhookom,
Thnx for ur reply. I have put in all the column heading and row heading and values into my query that I need. Also some of the values in the matrix are 0 or NULL. Well if I create a report based on this query, it says cannot recognise Inquiries.ID. Hence I read somewhere that these NULL values can be handled by having a 2nd select query. And thats the reason Y I have a 2nd query.

Here is the sql stmt for the crosstab query:

TRANSFORM Count(Inquiries.ID) AS CountOfID
SELECT Inquiries.Country
FROM Inquiries
WHERE (((Inquiries.Recruiting_Source)<>"Empty"))
GROUP BY Inquiries.Country
PIVOT Inquiries.Recruiting_Source;

The query runs fine and the report is also created fine just that it takes too much time and also it hangs up when I try to save the report.

 

[dhookom]

If all your Recruiting_Source values are known at design time, you can enter them all in to the Column Headings property of the crosstab. This should improve performance. You should also have an index created on the Inquiries.Country field.

You can change null values to 0 by using a Value of

Field: Val(Nz(Count(Inquiries.ID),0))
Total: Expression
Crosstab: Value

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]