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

K

Kay

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
 
T

tina

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
 
K

Kay

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

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


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).
Click to expand...
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.
Click to expand...
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");
Click to expand...
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;
Click to expand...
Thank you,
K
Click to expand...
Click to expand...
 
T

tina

no, that was it, sorry.


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

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


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).
Click to expand...
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.
Click to expand...
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
Click to expand...
("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;
Click to expand...
Thank you,
K
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

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 said:
didnt work...showed the same information as before. any other ideas?

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


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).
Click to expand...
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.
Click to expand...
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");
Click to expand...
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;
Click to expand...
Thank you,
K
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query fields results into a make table? 9
Crosstab query by date range 7
Crosstab queries as subforms error: "You can't use a pass-through. 3
Crosstab query columns not adding correctly 1
Crosstab query by quarter nightmare 9
Crosstab query to determine ving margins 4
Performing expression on all (varying number of) fields 3
Crosstab query will work one way but not the other 2

Top