Help needed for crosstab query

[Randy Harris]

I've been trying to construct a Crosstab query (mess pasted below) that
would output all rows, even if the Value (a count) is 0. No matter what I
do, it only returns rows that have matching records. Is it possible to do
this? I have a report based on the query that needs to show all of the
products and activities, even if the EventID is 0.

I'm grateful for any suggestions.
TRANSFORM Count(TrainingC enterSummary_qr y.EventID) AS CountOfEventID
SELECT TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity, TrainingCenterS ummary_qry.Cour seNumber,
Count(TrainingC enterSummary_qr y.EventID) AS [Total Of EventID]
FROM TrainingCenterS ummary_qry
GROUP BY TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity, TrainingCenterS ummary_qry.Cour seNumber
PIVOT TrainingCenterS ummary_qry.Clas sroomCity;

[Randy Harris]

"Chuck Grimsby" <c.*******@worl dnet.att.net.in valid> wrote in message
news:9c******** *************** *********@4ax.c om...

Does the TrainingCenterS ummary_qry show all the events?
Yes, all of the events appear in that query. What I need, is to have the
report include all possible row and column combinations, whether there is a
corresponding event or not. I've tried joining the table to itself and
every other combination I can think of, but can't get the crosstab to
include the rows that don't have matching records.
If not, you need to fix that, not the crosstab.

On Sat, 22 Nov 2003 09:34:18 GMT, "Randy Harris" <ra***@SpamFree .com>
wrote:

I've been trying to construct a Crosstab query (mess pasted below) that
would output all rows, even if the Value (a count) is 0. No matter what Ido, it only returns rows that have matching records. Is it possible to dothis? I have a report based on the query that needs to show all of the
products and activities, even if the EventID is 0.
I'm grateful for any suggestions.

TRANSFORM Count(TrainingC enterSummary_qr y.EventID) AS CountOfEventID
SELECT TrainingCenterS ummary_qry.Prod uctName,
TrainingCenter Summary_qry.Act ivity, TrainingCenterS ummary_qry.Cour seNumber,Count(Training CenterSummary_q ry.EventID) AS [Total Of EventID]
FROM TrainingCenterS ummary_qry
GROUP BY TrainingCenterS ummary_qry.Prod uctName,
TrainingCenter Summary_qry.Act ivity, TrainingCenterS ummary_qry.Cour seNumberPIVOT TrainingCenterS ummary_qry.Clas sroomCity;

--
Party On Dudes!

[Randy Harris]

Anyone know if there is a way to "trick" the crosstab into outputting all of
the rows, even if the count is 0?

"Randy Harris" <ra***@SpamFree .com> wrote in message
news:u4******** **************@ newssvr28.news. prodigy.com...

I've been trying to construct a Crosstab query (mess pasted below) that
would output all rows, even if the Value (a count) is 0. No matter what I
do, it only returns rows that have matching records. Is it possible to do
this? I have a report based on the query that needs to show all of the
products and activities, even if the EventID is 0.

I'm grateful for any suggestions.
TRANSFORM Count(TrainingC enterSummary_qr y.EventID) AS CountOfEventID
SELECT TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity, TrainingCenterS ummary_qry.Cour seNumber, Count(TrainingC enterSummary_qr y.EventID) AS [Total Of EventID]
FROM TrainingCenterS ummary_qry
GROUP BY TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity, TrainingCenterS ummary_qry.Cour seNumber
PIVOT TrainingCenterS ummary_qry.Clas sroomCity;

[DFS]

"Randy Harris" <ra***@SpamFree .com> wrote in message
news:U0******** **************@ newssvr28.news. prodigy.com...

Anyone know if there is a way to "trick" the crosstab into outputting all of the rows, even if the count is 0?

Yes, there is a way.

Change the last line of the Crosstab (the PIVOT line) so it's like this:

PIVOT TableName.Field Name in ('Field1','Fiel d2','Field3','F ield4',...)



"Randy Harris" <ra***@SpamFree .com> wrote in message
news:u4******** **************@ newssvr28.news. prodigy.com...

I've been trying to construct a Crosstab query (mess pasted below) that
would output all rows, even if the Value (a count) is 0. No matter what I do, it only returns rows that have matching records. Is it possible to do this? I have a report based on the query that needs to show all of the
products and activities, even if the EventID is 0.

I'm grateful for any suggestions.
TRANSFORM Count(TrainingC enterSummary_qr y.EventID) AS CountOfEventID
SELECT TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity,

TrainingCenterS ummary_qry.Cour seNumber,

Count(TrainingC enterSummary_qr y.EventID) AS [Total Of EventID]
FROM TrainingCenterS ummary_qry
GROUP BY TrainingCenterS ummary_qry.Prod uctName,
TrainingCenterS ummary_qry.Acti vity, TrainingCenterS ummary_qry.Cour seNumber PIVOT TrainingCenterS ummary_qry.Clas sroomCity;