How do I make this report???

  • Thread starter Thread starter Timothy.Rybak
  • Start date Start date
T

Timothy.Rybak

I have a table that has the following data:

Date ErrorCode NumberOfErrors

There are roughly 20 different error codes, and every day an operator
enters in the number of errors that occurred the day before.

The report that I want to pull from this data is a summary of quanity
of errors per month in the following format:

Defect Code Nov Dec Jan
D01 5 4 6
D02 3 2 5
D03 2 7 2
D04 6 4 1
D05 9 3 6
..
..
..

If you need more info, please let me know.

Z
 
Create a crosstab query with Defect Code as the Row Heading,
Format([Date],"mmm") as the Column Heading, and Sum(NumberOfErrors) as the
Value. Set the Column Headings property to: "Jan", "Feb",
"Mar","Apr",...."Dec".
 
I really appreciate the help. I've never used a crosstab query before,
but I got it to work on the first try! The only problem that I see is
that I get column headings for Jan, Feb, Mar, but they aren't year
indepentdant. In other words, the data from Jan 05 and Jan 06 is
combined together, instead of rolling over for the new year. I tried
changing the date format to "mmm/y" but it didn't work.

Any ideas on how to tweak this?

Z
 
Set a Row Heading of

Year([Date])

This will place different years in different rows.
 
Duane,

Thank you for the help so far. I hope that you will respond to this as
well. I found an older post from you that actually came up with a
different, and I think better (for me) solution. Here is the text of
that post, and I will follow with a few questions:

I do a lot of work with crosstabs and reporting from them. I generally
want
a crosstab to return a specific number of months' data for instance 3
months
or 6 or 12... To get these results, I use "relative" months so users
can
enter an ending date and my crosstab will display 6 months ending in
the
month entered.

The ending date would be a text box on a form Forms!frmA!txtEndDate
The columns in the crosstab are built using the expression
ColHead: "Mth" & DateDiff("m",[YourDateField],Forms!frmA!txtEndDate)
Set the Column Headings property to:
"Mth5","Mth4",..."Mth0"
The Mth0 column will be the month of the date entered in the text box.
Mth5
would be 5 months earlier.
When using a parameter in a crosstab query, you must select
Query|Parameters
and enter the
Forms!frmA!txtEndDate Date/Time

When using this query in a report, you can use text boxes for column
labels.
The control sources would be:
=DateAdd("m", -0, Forms!frmA!txtEndDate)
=DateAdd("m", -1, Forms!frmA!txtEndDate)
=DateAdd("m", -2, Forms!frmA!txtEndDate)
...
=DateAdd("m", -5, Forms!frmA!txtEndDate)

I got this working, and it shows the rolling months based on the end
date. Brilliant. The query works great. However, I used the report
wizard to create the initial report based on the query. When I open
the report, it pops up a text box asking for a value for tMain.ID.
tMain is my only table (where everything is stored) and obviously ID is
the autogenerated primary key field. I don't use this field anywhere
in either the query or the report. Any idea why it is asking for this
value, and how I can get it to stop?

Also, based on this solution, the column headings in the report show
the entire date (1/31/2006, 12/31/2005, etc). Is there a way to get
them to just display the month and year?

Thanks!
Tim
 
I don't know why you are getting a prompt for ID. Did you check the Sorting
and Grouping levels?

To display a different format in your column headings, change the Format
property of the text boxes to what ever you want.
 
The column headings work great, thanks for the tip!

The ID prompt is killing me... it's the last piece of this puzzle. As
I said, the crosstab query runs without an issue. It has 3 colums as
follows:

Field: Error
Table: tMain
Total: Group By
Crosstab: Row Heading

Field: "Mth" & DateDiff("m",tMain!Date,Forms![fMain Top 5
Defects]!cEndDate)
Table: Left Blank
Total: Group By
Crosstab: Column Heading

Field: Qty of Errors
Table: tMain
Total: Sum
Crosstab: Value

I have one parameter set up as:

[Forms]![fMain Top 5 Defects]![cEndDate] = Date/Time (it uses a
calendar picker)

I have gone over and over the report and the form, and I cannot find
any reference to tMain.ID. Can you think of anywhere else I can look?

Z
 
Back
Top