Displaying a Crosstab query on a Report

[Kamille]

Hi,
I have developed a "Crosstab Query" based on an existing query. Works great...looks like:
Location
Date Canada U.S. Europe Asia
Jan 4 5 3 10
Feb 5 5 6 5
Mar 1 1 9 6

Now I would like to display this information(looking like the crosstab above)in a Report.
So I went into "Report" "New" "Report Wizard" and when I selected my crosstab query as the source of data the field list was empty.

How would I do this?

Thanks,
Kamille

 

[dhookom]

In the query design view, set the Column Headings property to all the potential columns:
Canada, U.S., Europe, Asia,...

Duane
MS Access MVP

 

[Kamille]

Thanks Duane,
but I'm still having trouble. I would like to base my report on the parameter based crosstab query. The Query when I run it asks for a Date Range (i.e jan 1 2003 to oct 31 2003...by the way Happy Halloween!)Also note: the field [prevdate] I used to capture the date I formatted "Date: (format([prevdate], "mmmm yyyy&quot so it can just sum info. for each month and not individual dates. So then the query provides a crosstab which looks great. I just want my report to look exactly like the crosstab. When I open Report Wizard the field list is empty. So I went to the crosstab query and went to properties and under "column headings" added column headings, but i'm still missing a field. I get a "unknown" field instead. Is there any easy way to get a crosstab report template?

 

[dhookom]

If your crosstab has parameter criteria, you must explicitly enter the parameters and their data types. Select Query|Parameters and enter something like:
[Enter Start Date] Date/Time
[Enter End Date] Date/Time
I much prefer to use references to text boxes on a form for this.
The column for [PrevDate] should look like:
Field: PrevDate
Total: Where
Crosstab:
Criteria: Between [Enter Start Date] AND [Enter End Date]


Duane
MS Access MVP

 

[Kamille]

Thanks a bunch! It works!
Kamille!

 

[PamCF]

I see this is an older post, but I am having the same problem with trying to put a crosstab query into a report. You say set the Columnheadings Property, but what if you don't know what values are going to be displayed? The columns depend on selection criteria in a query that is run to select the data for the crosstab query. The columns will change depending on the data selected.

 

[randysmid]

Pam,
I wrote an FAQ on how to use the Crosstab wizard. It is [faq703-2868]

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[dhookom]

Randy,
Nice FAQ but it doesn't address dynamic column headings. For instance, if not all months are represented, the report will fail. If dealing only with months, you could enter all of the month numbers in the Column Headings property of the crosstab.
Column Headings: 1,2,3,4...12
If you don't have a clue as to which column names might get generated, you can still create a report. The best solution for this is found in a crosstab report demo at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

I wouldn't even begin to try implement the solution found in the Solutions.mdb.

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

 

[randysmid]

Hi,
Personally, I like to use two or more queries to generate the crosstab query. I use the first one to limit those records selected by the user (I use a form with all the various selection criteria). The first query will then generate a list (such as all California customers, or a range of order dates), and this is then fed into the crosstab query. There is a distinct advantage in breaking it all down into two or more queries: it is much easier to debug!!!

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association