Create a report from a cross-tab query (1 Viewer)

marcusblackcat · Mar 10, 2016

I know how to do the above - but I am having an issue.

I have the attached result from a crosstab query (Names change rto protect the innocent!)

These are currently open cases and their level - I want to create a report from this information, however, there are 9 total levels of case (so should be level 0 - level 8) but the query returns only the "levels" which have numbers in - there are currently no cases at level 8.

How do I set up my report so that it will display levels 0-8 with no values in 8 - when I pull the report together I gen an error stating that the system cannot find the field "Level 8" which, as the query brings back no results for it, is logical - but I can;t find a way around it! :banghead:

Ranman256 · Mar 10, 2016

Create a 'report' table that has all the fields in it, then run an append the Crosstab data to it.
By turning off warnings, a macro can run a dozen append queries,each appends only 1 of the column data. If that column doesn't exist,it ignores it and moves on.

Once all the queries have appended to the rpt tbl, a final query sums all columns in the rpt tbl to remove blanks and everything shows on single lines.

marcusblackcat · Mar 10, 2016

Ranman256 said:
Create a 'report' table that has all the fields in it, then run an append the Crosstab data to it.
By turning off warnings, a macro can run a dozen append queries,each appends only 1 of the column data. If that column doesn't exist,it ignores it and moves on.

Once all the queries have appended to the rpt tbl, a final query sums all columns in the rpt tbl to remove blanks and everything shows on single lines.

Thanks Ranman - will have a go at that this afternoon after lunch!

JHB · Mar 10, 2016

marcusblackcat said:
..
These are currently open cases and their level - I want to create a report from this information, however, there are 9 total levels of case (so should be level 0 - level 8) but the query returns only the "levels" which have numbers in - there are currently no cases at level 8.

You could use the IN clause:

Code:

... PIVOT [Level] IN ("Level 0", "Level 1", ...., "Level 8");

Create a report from a cross-tab query (1 Viewer)

marcusblackcat

New member

Attachments

Ranman256

Well-known member

marcusblackcat

New member

JHB

Have been here a while

Similar threads

Users who are viewing this thread