Report formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
JD said:
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.
Click to expand...


Normally, a crosstab would be the best way, but you do not
have any data to use for row headers.

About the only hope I can see for this is to position the
text boxes in a column at the left edge of the detail
section. Then use the File - Page Setup -Columns menu item
to specify as many columns as you can fit inside the
margins. Be sure to select the Across then Down snaking
option. You will probably also want to uncheck the Same As
Detail box and specify the column width manually.
 
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.
 
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
 
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
 
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
 
Back
Top