How to make this cross tab query

[Guest]

Hi all,

I like to thank those who has helped me a lot with my other posts,
especially since I barely have any idea of what I am doing.

I need any help at all on how to create this cross tab query. There are
four fields in this query, but for the count, only one field is relavent.

The fields are:
Reportorder, ID, Icat, and Dateclosed.

Reportorder is between numbers 1 to 12

report output will look like this:

E E/P P Total

Assmt in process 0 2 0
2

Resolut in process 1 2 2
5

Pend Implmt 0 0 2
2

Open Issue
9

Closed Issue 31 11 20
62

Total Issues 32 15 24 71

Total Issues - Total is the sum of Open and closed Issue.

The Reportorder number corresponds with each spot. Reportorder 1 = Assmt in
process E, Reportorder 2 = Assmt in process E/P. 3 for Assmt, 3 for Resolut,
3 for Pending, and 3 for Closed.

I beleive this can be done with a cross tab query, but I'm not skilled
enough to figure it out. I hope anyone can help me. If I haven't completely
confused you and you need more information, please let me know. Thank you

 

[Guest]

I can't see which field the E, E/P,... are coming from. Also, you state the
total issues is the sum of "Open and closed Issue" but your total seems to
include the in process and pending also.

I'm sure this can be done in a crosstab if we knew more about your desired
output. Are the numbers displayed counts or sums or averages or what?

 

[Guest]

Hi Duane,

Everything comes from the Reportorder field. There are 12 results in 4
different categories. The 4 categories are Assmt, Resolut, Pending, and
closed. In each category, there are 3 results. Assmt E (1), Assmt E/P (2),
Assmt P (3), Resolut E (4), Resolut E/P (5), Resolut p (6), Pending E (7),
Pending E/P (8), Pending P (9), Closed E(10), Closed E/P (11), and Closed P
(12).

The report order number corresponds to each result, 1 to 12 in the same
order as I listed above.

Each column will count how many of each result there is. So if there are
four 1's, the count is 4, two 2's count is 2, five 3's count is 5, etc.

E E/P P
Total

Assmt in process 0 2 0 2
Resolut in process 1 2 2 5
Pend Implmt 0 0 2 2
Open Issue 9

Closed Issue 31 11 20 62

Total Issues 32 15 24 71

For the example I just placed, there are total of 71 records. There are a
total of 9 open issues and 62 closed issues. The total Issues counts each
totals for E, E/P, P and Total.

I hope this explaination helps more. Thank you.

 

[Guest]

Create a table with 3 fields and 12 records. The first field (ReportOrder)
would contain the numbers 1 -12 while the other two fields would contain
values for the Row Heading and the Column Heading values. Add this table to
your crosstab query and join the ReportOrder fields. Add the row and column
heading fields to the grid and set their crosstab value as noted. Add a field
to the grid that you can count or whatever you want. You can add another row
heading that sums or counts all records by your first row heading field.