Pivot Table into Report

[Scott Kolesar]

I'm trying to generate a weekly report for management that summarizes Help
Desk activity. I'm using Access DB that links to a CSV, generated from the
support system (SQL not accessible).

My problem is getting the figures I can generate in the Pivot Table into a
form I can just pull up without further editing. I've already separated out
by type of request (Break/Fix and Service Request), generating separate
queries.

What is requested are stats for various service queues(rows), by status
(columns), grouped in specific ways; some queues are not grouped, some need
to appear separately, others are dropped into a 'misc' group. status, too,
is grouped. This can be accomplished by a PivotTable. If I could copy and
pasts, I'd be done, but I can only copy off the numbers.

*- If I try to report on a Crosstab query, I cannot seem to perform the
custom grouping I need without further editing.
*- I'm thinking I can requery on the crosstab, and do my groupings there,
but I don't know how to break up the fields.

**Any ideas on which direction to go are appreciated.


Here's a genericized sample query:
[sorry if you're not reading this fixed-pitched]
Pivot Table:

Queue Status
Open (Grouped) Closed (Grouped) TOTAL

Queue 1 3 17 20
Queue 2 2 2
Queue 3 1 6 7
Grouped (3 queues) 11 11
Misc. (variable #) 3 7 10
-------------------------------------------------------------------
GRAND TOTAL 7 43 50


CrossTab:


Queue Status
Assigned In_Prog Pend Closed Resolved Cancelled
TOTAL
Queue 1 1
1
Queue 2 1
1
Queue 3 1
1
Queue 4 1 2 5 12
20
Queue 5 2
2
Queue 6 1 6
7
Queue 7 1
1
Queue 8 1
1
Queue 9 2
2
Queue 10 4 7
11
Queue 11 1
1
Queue 12 1
1
Queue 13 1
1

 

[Duane Hookom]

I would model all of this in tables with values that identify which queues
are grouped together etc. Then create a crosstab for your report.

--
Duane Hookom
Microsoft Access MVP

I'm trying to generate a weekly report for management that summarizes Help
Desk activity. I'm using Access DB that links to a CSV, generated from the
support system (SQL not accessible).

My problem is getting the figures I can generate in the Pivot Table into a
form I can just pull up without further editing. I've already separated out
by type of request (Break/Fix and Service Request), generating separate
queries.

What is requested are stats for various service queues(rows), by status
(columns), grouped in specific ways; some queues are not grouped, some need
to appear separately, others are dropped into a 'misc' group. status, too,
is grouped. This can be accomplished by a PivotTable. If I could copy and
pasts, I'd be done, but I can only copy off the numbers.

*- If I try to report on a Crosstab query, I cannot seem to perform the
custom grouping I need without further editing.
*- I'm thinking I can requery on the crosstab, and do my groupings there,
but I don't know how to break up the fields.

**Any ideas on which direction to go are appreciated.


Here's a genericized sample query:
[sorry if you're not reading this fixed-pitched]
Pivot Table:

Queue Status
Open (Grouped) Closed (Grouped) TOTAL

Queue 1 3 17 20
Queue 2 2 2
Queue 3 1 6 7
Grouped (3 queues) 11 11
Misc. (variable #) 3 7 10
-------------------------------------------------------------------
GRAND TOTAL 7 43 50


CrossTab:


Queue Status
Assigned In_Prog Pend Closed Resolved Cancelled
TOTAL
Queue 1 1
1
Queue 2 1
1
Queue 3 1
1
Queue 4 1 2 5 12
20
Queue 5 2
2
Queue 6 1 6
7
Queue 7 1
1
Queue 8 1
1
Queue 9 2
2
Queue 10 4 7
11
Queue 11 1
1
Queue 12 1
1
Queue 13 1
1