Report based of a crosstab query

[Sean]

In my cross tab query I show the persons name, time range (6-7am, 7-8am,
etc), and number of orders pulled for each hour. If I run the report for the
previous days work I hve no problems, however when I run it for the current
day, say mid day 11am, it errors out becuase there is no data past 11am .
The report doesn't recognize those fileds becuase they are not in the query.

How do I redo this report so it will not error out.

The query is:

TRANSFORM Avg(IO_KO_Time.IO_KO) AS AvgOfIO_KO
SELECT IO_KO_Time.Name, Max(IO_KO_Time.IO_KO) AS [Max IO_KO]
FROM IO_KO_Time
GROUP BY IO_KO_Time.Name
PIVOT IO_KO_Time.TFTTIME;

Results are:

Name: 1 2 3
Sean 22 44 36

So if there is no data for the 4th hour on, the report errors out as it does
not recognize the fields 4, 5, 6, etc.

Thanks,
Sean

 

[Dale Fye]

Add an IN clause to the query:

PIVOT IO_KO_Time.TFTTIME IN ("6-7am", "7-8am", "8-9am", ...)

You can also do this in the query design, but setting the ColumnHeaders
property.

Make sure that the values are exactly the way they appear when run against
yesterdays data, or they will be blank.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Sean]

You are a rockstar. Thanks

Add an IN clause to the query:

PIVOT IO_KO_Time.TFTTIME IN ("6-7am", "7-8am", "8-9am", ...)

You can also do this in the query design, but setting the ColumnHeaders
property.

Make sure that the values are exactly the way they appear when run against
yesterdays data, or they will be blank.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

In my cross tab query I show the persons name, time range (6-7am, 7-8am,
etc), and number of orders pulled for each hour. If I run the report for the
previous days work I hve no problems, however when I run it for the current
day, say mid day 11am, it errors out becuase there is no data past 11am .
The report doesn't recognize those fileds becuase they are not in the query.

How do I redo this report so it will not error out.

The query is:

TRANSFORM Avg(IO_KO_Time.IO_KO) AS AvgOfIO_KO
SELECT IO_KO_Time.Name, Max(IO_KO_Time.IO_KO) AS [Max IO_KO]
FROM IO_KO_Time
GROUP BY IO_KO_Time.Name
PIVOT IO_KO_Time.TFTTIME;

Results are:

Name: 1 2 3
Sean 22 44 36

So if there is no data for the 4th hour on, the report errors out as it does
not recognize the fields 4, 5, 6, etc.

Thanks,
Sean