Another cross-tab query

[DebbieG]

This cross-tab query is working great except that I want to show all current
participants even if they haven't attended an event. When I tried to change
the join type between the Students table and the Events table it told me
that it would cause an ambiguous outer joins. So if I can't change the join
type, how can I get all current participants even if they aren't in the
EventsStudents table.

PARAMETERS [Forms]![frmAttendancePercent]![txtBeginDate] DateTime,
[Forms]![frmAttendancePercent]![txtEndDate] DateTime;
TRANSFORM Count(EventsStudents.EventStatusCD) AS CountOfEventStatusCD
SELECT [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name
FROM (Students INNER JOIN (EventTypeCD INNER JOIN (Events INNER JOIN
EventsStudents ON Events.EventID = EventsStudents.EventID) ON
EventTypeCD.EventTypeCD = Events.EventTypeCD) ON Students.SSN =
EventsStudents.SSN) INNER JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
WHERE (((EventsStudents.EventStatusCD)="P") AND ((Events.EventDate) Between
[Forms]![frmAttendancePercent]![txtBeginDate] And
[Forms]![frmAttendancePercent]![txtEndDate]) AND
((OtherInfo.CurrentPart)=Yes))
GROUP BY [LastNM] & ", " & [FirstNM] & " " & [MI]
PIVOT EventTypeCD.EventType;

Thanks,
Debbie

 

[DebbieG]

I got it to work! This is how I changed the cross-tab query:

PARAMETERS [Forms]![frmAttendancePercent]![txtBeginDate] DateTime,
[Forms]![frmAttendancePercent]![txtEndDate] DateTime;
TRANSFORM Count(EventsStudents.EventStatusCD) AS CountOfEventStatusCD
SELECT Students.SSN, [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name
FROM (Students INNER JOIN (Events INNER JOIN EventsStudents ON
Events.EventID = EventsStudents.EventID) ON Students.SSN =
EventsStudents.SSN) INNER JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
WHERE (((Events.EventDate) Between
[Forms]![frmAttendancePercent]![txtBeginDate] And
[Forms]![frmAttendancePercent]![txtEndDate]) AND
((OtherInfo.CurrentPart)=Yes))
GROUP BY Students.SSN, [LastNM] & ", " & [FirstNM] & " " & [MI]
PIVOT Switch(Events.EventTypeCD="1" And
EventStatusCD="P","EveP",Events.EventTypeCD="2" And
EventStatusCD="P","ManP",Events.EventTypeCD="3" And
EventStatusCD="P","OptP") In ("EveP","ManP","OptP");

Than I created a select query which looks like this:

PARAMETERS [Forms]![frmAttendancePercent]![txtBeginDate] DateTime,
[Forms]![frmAttendancePercent]![txtEndDate] DateTime;
SELECT Students.SSN, [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name,
Students.LastNM, Students.FirstNM, OtherInfo.CurrentPart,
qxtbAttendancePercent.EveP, qxtbAttendancePercent.ManP,
qxtbAttendancePercent.OptP
FROM (Students LEFT JOIN qxtbAttendancePercent ON Students.SSN =
qxtbAttendancePercent.SSN) INNER JOIN OtherInfo ON Students.SSN =
OtherInfo.SSN
WHERE (((OtherInfo.CurrentPart)=Yes))
ORDER BY Students.LastNM, Students.FirstNM;

Sorry to bother you.
Debbie


This cross-tab query is working great except that I want to show all current
participants even if they haven't attended an event. When I tried to change
the join type between the Students table and the Events table it told me
that it would cause an ambiguous outer joins. So if I can't change the join
type, how can I get all current participants even if they aren't in the
EventsStudents table.

PARAMETERS [Forms]![frmAttendancePercent]![txtBeginDate] DateTime,
[Forms]![frmAttendancePercent]![txtEndDate] DateTime;
TRANSFORM Count(EventsStudents.EventStatusCD) AS CountOfEventStatusCD
SELECT [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name
FROM (Students INNER JOIN (EventTypeCD INNER JOIN (Events INNER JOIN
EventsStudents ON Events.EventID = EventsStudents.EventID) ON
EventTypeCD.EventTypeCD = Events.EventTypeCD) ON Students.SSN =
EventsStudents.SSN) INNER JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
WHERE (((EventsStudents.EventStatusCD)="P") AND ((Events.EventDate) Between
[Forms]![frmAttendancePercent]![txtBeginDate] And
[Forms]![frmAttendancePercent]![txtEndDate]) AND
((OtherInfo.CurrentPart)=Yes))
GROUP BY [LastNM] & ", " & [FirstNM] & " " & [MI]
PIVOT EventTypeCD.EventType;

Thanks,
Debbie