Report formatting

J

JD

I have a table with the following columns: Month, Field1, Field2, Field3 - Fields 1 - 3 are retrieved from total queries

I am struggling with the manner to print a time-series report, with month going across the top

I've exhausted my knowledge of report formatting, and although I believe incorrect, I've tried several times to set up cross tab queries, running into a problem because only one value can be declared

?What is the best way to format a time series report (I apologize if this posts twice, but my last effort to post appears to have been unsuccessful

Thanks
 
J

JD

Marshall

I appreciate your response and wanted to make sure I had properly explained myself
I have a table that lists incidents. These incidents can then be Open/Closed and On-time/Late
If I total them in query mode, I'll end up wit

Total Closed Closed On time
Jan 15 10
Feb 12 8

I want a report in the form o

Jan Fe
Total 15 1
Closed 10
On-time 5

In cross-tab mode, I get the error that I can only have one value, but I must be doing something wrong ~ Any thoughts, or is your original post still my best path.
 
M

Marshall Barton

JD said:
I appreciate your response and wanted to make sure I had properly explained myself.
I have a table that lists incidents. These incidents can then be Open/Closed and On-time/Late.
If I total them in query mode, I'll end up with


Total Closed Closed On time
Jan 15 10 5
Feb 12 8 7

I want a report in the form of

Jan Feb
Total 15 12
Closed 10 8
On-time 5 7

In cross-tab mode, I get the error that I can only have one value, but I must be doing something wrong ~ Any thoughts, or is your original post still my best path.
Click to expand...


What **exactly** are the fields (along with their type and
some sample values) in the table (not some query)?

If it isn't already there, we need to get the data into a
structure that looks like:

Dates Status
 
J

JD

Table follows below: I am trying to group on month (Oct, Nov, etc..) and want to tabulate 1)all action id's, 2)all action id's where [status] = "C", and 3) all action id's where [on-time] = "Yes"

Field Name ActionID Completion Date Status On-time
Type Autonumber Date Text Text

1 10/30/2001 C No
2 10/30/2001 C No
3 11/22/2001 C No
4 11/22/2001 C No
5 11/22/2001 C No
6 11/22/2001 C No
7 11/22/2001 C No
8 11/22/2001 C No
9 11/22/2001 C Yes
10 11/22/2001 C Yes
 
M

Marshall Barton

JD said:
Table follows below: I am trying to group on month (Oct, Nov, etc..) and want to tabulate 1)all action id's, 2)all action id's where [status] = "C", and 3) all action id's where [on-time] = "Yes"

Field Name ActionID Completion Date Status On-time
Type Autonumber Date Text Text

1 10/30/2001 C No
2 10/30/2001 C No
3 11/22/2001 C No
4 11/22/2001 C No
5 11/22/2001 C No
6 11/22/2001 C No
7 11/22/2001 C No
8 11/22/2001 C No
9 11/22/2001 C Yes
10 11/22/2001 C Yes
Click to expand...


Ok, that looks good as is. The issue of getting a Total is
a slightly different query from the one that gets the Closed
and On-time so we're going to UNION the two sets together.

Create a query named qryTotal like this:
TRANSFORM Count(*) AS [The Value]
SELECT "Total" As RowHead
FROM thetable
GROUP BY "Total"
PIVOT Format([Completion Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

and another query named qryClosed:
TRANSFORM Count(*) AS [The Value]
SELECT IIf([On-Time]="Yes","On Time","Closed") As RowHead
FROM Absences
WHERE Status = "C"
GROUP BY IIf([On-Time]="Yes","On Time","Closed")
PIVOT Format([AbsenceDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Now you can base the report on the query:
SELECT * FROM qryTotal
UNION ALL
SELECT * FROM qryClosed
 
Top