Make Crosstab Query Display All Event Types even if Event Type not chosen by any participant

Hello - I have my database all worked out and I am totally
excited....just one more thing. My crosstab query sums up the number
of event types (eventtypeID) by month (activitydate) - good. However,
if one of the event types is not chosen for any of the participants,
then it is not listed on the query output table. I want all of the
options that I have as events to be listed on the query table even if
its not chosen by anyone (with the event's monthly counts showing a 0
of course).

Heres my code. Let me know if you need more info. I've tried to find
info on the net and put that 'or is null' next to the criteria field
in the crosstab under EventTypeID...didnt work, all the data in my
query disappeared in the table view.


CROSSTAB QUERY
TRANSFORM Nz(Count([CountOfEventTypeID]),0) AS Expr1
SELECT [Part Count].EventTypeID, Count([Part
Count].CountOfEventTypeID) AS [Total Of CountOfEventTypeID]
FROM [Part Count]
GROUP BY [Part Count].EventTypeID
PIVOT Format([ActivityDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

WHICH IS BASED OFF THIS QUERY (Part Count)
SELECT [Activity Tracker].EventTypeID, Count([Activity
Tracker].ActivityTrackerID) AS CountOfActivityTrackerID, [Activity
Tracker].ActivityDate, Count([Activity Tracker].EventTypeID) AS
CountOfEventTypeID
FROM [Activity Tracker]
GROUP BY [Activity Tracker].EventTypeID, [Activity
Tracker].ActivityDate;


Thank you,
K

try adding the EventType table to the base query, with a LEFT JOIN *from*
the EventType table *to* the ActivityTracker table, on their common fields.
to get a LEFT JOIN, set the link to "show all records from the EventType
table, and only matching records from the ActivityTracker table". use the
EventTypeID field from the EventType table, rather than the ActivityTracker
table, in the query output.

hth


"Kay" wrote in message
oups.com...
Hello - I have my database all worked out and I am totally
excited....just one more thing. My crosstab query sums up the number
of event types (eventtypeID) by month (activitydate) - good. However,
if one of the event types is not chosen for any of the participants,
then it is not listed on the query output table. I want all of the
options that I have as events to be listed on the query table even if
its not chosen by anyone (with the event's monthly counts showing a 0
of course).

Heres my code. Let me know if you need more info. I've tried to find
info on the net and put that 'or is null' next to the criteria field
in the crosstab under EventTypeID...didnt work, all the data in my
query disappeared in the table view.


CROSSTAB QUERY
TRANSFORM Nz(Count([CountOfEventTypeID]),0) AS Expr1
SELECT [Part Count].EventTypeID, Count([Part
Count].CountOfEventTypeID) AS [Total Of CountOfEventTypeID]
FROM [Part Count]
GROUP BY [Part Count].EventTypeID
PIVOT Format([ActivityDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

WHICH IS BASED OFF THIS QUERY (Part Count)
SELECT [Activity Tracker].EventTypeID, Count([Activity
Tracker].ActivityTrackerID) AS CountOfActivityTrackerID, [Activity
Tracker].ActivityDate, Count([Activity Tracker].EventTypeID) AS
CountOfEventTypeID
FROM [Activity Tracker]
GROUP BY [Activity Tracker].EventTypeID, [Activity
Tracker].ActivityDate;


Thank you,
K

didnt work...showed the same information as before. any other ideas?

On Feb 11, 11:56 pm, "tina" wrote:
try adding the EventType table to the base query, with a LEFT JOIN *from*
the EventType table *to* the ActivityTracker table, on their common fields.
to get a LEFT JOIN, set the link to "show all records from the EventType
table, and only matching records from the ActivityTracker table". use the
EventTypeID field from the EventType table, rather than the ActivityTracker
table, in the query output.

hth

"Kay" wrote in message

oups.com...

Hello - I have my database all worked out and I am totally
excited....just one more thing. My crosstab query sums up the number
of event types (eventtypeID) by month (activitydate) - good. However,
if one of the event types is not chosen for any of the participants,
then it is not listed on the query output table. I want all of the
options that I have as events to be listed on the query table even if
its not chosen by anyone (with the event's monthly counts showing a 0
of course).

Heres my code. Let me know if you need more info. I've tried to find
info on the net and put that 'or is null' next to the criteria field
in the crosstab under EventTypeID...didnt work, all the data in my
query disappeared in the table view.

CROSSTAB QUERY
TRANSFORM Nz(Count([CountOfEventTypeID]),0) AS Expr1
SELECT [Part Count].EventTypeID, Count([Part
Count].CountOfEventTypeID) AS [Total Of CountOfEventTypeID]
FROM [Part Count]
GROUP BY [Part Count].EventTypeID
PIVOT Format([ActivityDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

WHICH IS BASED OFF THIS QUERY (Part Count)
SELECT [Activity Tracker].EventTypeID, Count([Activity
Tracker].ActivityTrackerID) AS CountOfActivityTrackerID, [Activity
Tracker].ActivityDate, Count([Activity Tracker].EventTypeID) AS
CountOfEventTypeID
FROM [Activity Tracker]
GROUP BY [Activity Tracker].EventTypeID, [Activity
Tracker].ActivityDate;

Thank you,
K

no, that was it, sorry.


"Kay" wrote in message
ps.com...
didnt work...showed the same information as before. any other ideas?

On Feb 11, 11:56 pm, "tina" wrote:
try adding the EventType table to the base query, with a LEFT JOIN
*from*
the EventType table *to* the ActivityTracker table, on their common
fields.
to get a LEFT JOIN, set the link to "show all records from the EventType
table, and only matching records from the ActivityTracker table". use
the
EventTypeID field from the EventType table, rather than the
ActivityTracker
table, in the query output.

hth

"Kay" wrote in message

oups.com...

Hello - I have my database all worked out and I am totally
excited....just one more thing. My crosstab query sums up the number
of event types (eventtypeID) by month (activitydate) - good. However,
if one of the event types is not chosen for any of the participants,
then it is not listed on the query output table. I want all of the
options that I have as events to be listed on the query table even if
its not chosen by anyone (with the event's monthly counts showing a 0
of course).

Heres my code. Let me know if you need more info. I've tried to find
info on the net and put that 'or is null' next to the criteria field
in the crosstab under EventTypeID...didnt work, all the data in my
query disappeared in the table view.

CROSSTAB QUERY
TRANSFORM Nz(Count([CountOfEventTypeID]),0) AS Expr1
SELECT [Part Count].EventTypeID, Count([Part
Count].CountOfEventTypeID) AS [Total Of CountOfEventTypeID]
FROM [Part Count]
GROUP BY [Part Count].EventTypeID
PIVOT Format([ActivityDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

WHICH IS BASED OFF THIS QUERY (Part Count)
SELECT [Activity Tracker].EventTypeID, Count([Activity
Tracker].ActivityTrackerID) AS CountOfActivityTrackerID, [Activity
Tracker].ActivityDate, Count([Activity Tracker].EventTypeID) AS
CountOfEventTypeID
FROM [Activity Tracker]
GROUP BY [Activity Tracker].EventTypeID, [Activity
Tracker].ActivityDate;

Thank you,
K

Please show SQL that "didn't work"...

I wonder if you did not "use" EventTypeID from
"inner table," i.e., [Activity Tracker], instead of
EventTypeID from "outer table" (EventType table).

just a guess (if your table named "EventType")...

TRANSFORM Nz(Count(E.EventTypeID),0) AS MnthCnt
SELECT
E.EventTypeID,
Count(E.EventTypeID) AS TotCntForEventType,
FROM
EventType As E
LEFT JOIN
[Activity Tracker] As A
ON
E.EventTypeID = A.EventTypeID
GROUP BY
E.EventTypeID
PIVOT Format(A.[ActivityDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

Do you see how you might get "same info"
if you had used "A.EventTypeID" instead of
"E.EventTypeID?"

SELECT
E.EventTypeID,
A.EventTypeID,
Count(E.EventTypeID) As CntE,
Count(A.EventTypeID) As CntA
FROM
EventType As E
LEFT JOIN
[Activity Tracker] As A
ON
E.EventTypeID = A.EventTypeID
GROUP BY
E.EventTypeID,
A.EventTypeID;

Of course I may have completely misunderstood...

"Kay" wrote:
didnt work...showed the same information as before. any other ideas?

On Feb 11, 11:56 pm, "tina" wrote:
try adding the EventType table to the base query, with a LEFT JOIN *from*
the EventType table *to* the ActivityTracker table, on their common
fields.
to get a LEFT JOIN, set the link to "show all records from the EventType
table, and only matching records from the ActivityTracker table". use the
EventTypeID field from the EventType table, rather than the
ActivityTracker
table, in the query output.

hth

"Kay" wrote in message

oups.com...

Hello - I have my database all worked out and I am totally
excited....just one more thing. My crosstab query sums up the number
of event types (eventtypeID) by month (activitydate) - good. However,
if one of the event types is not chosen for any of the participants,
then it is not listed on the query output table. I want all of the
options that I have as events to be listed on the query table even if
its not chosen by anyone (with the event's monthly counts showing a 0
of course).

Heres my code. Let me know if you need more info. I've tried to find
info on the net and put that 'or is null' next to the criteria field
in the crosstab under EventTypeID...didnt work, all the data in my
query disappeared in the table view.

CROSSTAB QUERY
TRANSFORM Nz(Count([CountOfEventTypeID]),0) AS Expr1
SELECT [Part Count].EventTypeID, Count([Part
Count].CountOfEventTypeID) AS [Total Of CountOfEventTypeID]
FROM [Part Count]
GROUP BY [Part Count].EventTypeID
PIVOT Format([ActivityDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

WHICH IS BASED OFF THIS QUERY (Part Count)
SELECT [Activity Tracker].EventTypeID, Count([Activity
Tracker].ActivityTrackerID) AS CountOfActivityTrackerID, [Activity
Tracker].ActivityDate, Count([Activity Tracker].EventTypeID) AS
CountOfEventTypeID
FROM [Activity Tracker]
GROUP BY [Activity Tracker].EventTypeID, [Activity
Tracker].ActivityDate;

Thank you,
K