Report based on crosstab queries

[jgarnick]

I have a make table query that creates table A. I then have two different crosstab queries based table A. I need to create a report based on the data in both of these crosstab queries. Is this possible and how? I get an error message using the wizard because both crosstabs are based on the same table.

Thanks!

jgarnick
jgarnick@aol.com

 

[DougP]

You can probably create a report that has all the data you need but in a slightly different way.

I suspect in your crosstab query that you have something like Jan, Feb, Mar, and values under those columns

In the report you use the “Group by” function. Go in the “View” menu “Sorting and Grouping” you would pick Month field and it would group all data in Jan. together and Feb. together and so on.


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[BlindPete]

I had a similar problem. I had too merge a crosstab with a another query. I ended up creating a dummy recordset and combining there results in it. Then my report worked fine. It was ugly to get there but it worked. There must be a better way.
-Pete

 

[raskew]

Take a look at the explanations described in thread 181-42850 You'll find both a code and a query solution to a similar dilemma.

 

[jgarnick]

I ended up using a subreport and having the report based on one crosstab and the subreport the other. I laid the subreport over the fields in the report because I needed the data to be side by side. A little bit of clean up, but it works except for one small problem......

Is there a way to force the crosstab to list all the rows even if there is no data, like you can for columns by putting column headings in the query properties? I need to get column totals for the numbers in the subreport on the main report, but if I put an expression on the subreport in the report footer, it doesn't line up because the subreport's number of rows will vary. But if I can force the crosstab to list all rows all the time it will match up nicely!

Thanks!


jgarnick
jgarnick@aol.com