Hi Karl, below are the two queries
Main report qry:
SELECT DISTINCTROW [Speaker and sessions Query].SpeakerID,
Sessions.MeetingID, [Meeting Information].NewMeeting, [Speaker and sessions
Query].[Session#], [Speaker and sessions Query].[S/M], [Speaker and sessions
Query].NewTitle, [Speaker and sessions Query].NewFirstName, [Speaker and
sessions Query].NewLastName, [Speaker and sessions Query].NewOrganization,
[Speaker and sessions Query].NewAddress, [Speaker and sessions
Query].NewCityStateZip, [Speaker and sessions Query].NewPhone, [Speaker and
sessions Query].NewExt, [Speaker and sessions Query].NewFax, [Speaker and
sessions Query].NewCell, [Speaker and sessions Query].NewEmail, [Speaker and
sessions Query].ConfSent, [Speaker and sessions Query].ConfRcvd, [Speaker and
sessions Query].[CC'd], [Speaker and sessions Query].Paid, [Speaker and
sessions Query].Date, [Speaker and sessions Query].Time, [Speaker and
sessions Query].SessionName, Sessions.Description, Employees.FirstName & " "
& [LastName] AS Contact, Employees.Phone, Employees.Email, Rooms.Room,
Sessions.SessionID
FROM [Meeting Information] INNER JOIN (([Speaker and sessions Query] INNER
JOIN (Sessions LEFT JOIN Rooms ON Sessions.RoomID = Rooms.RoomID) ON [Speaker
and sessions Query].SessionID = Sessions.SessionID) LEFT JOIN Employees ON
[Speaker and sessions Query].EmployeeID = Employees.EmployeeID) ON [Meeting
Information].MeetingID = Sessions.MeetingID
WHERE ((([Speaker and sessions Query].[S/M]) Like "*Speak*") AND (([Speaker
and sessions Query].NewOrganization) Not Like "League of Ca*") AND (([Speaker
and sessions Query].NewEmail) Is Not Null) AND (([Speaker and sessions
Query].ConfSent) Is Null) AND (([Speaker and sessions Query].Paid) Not Like
-1))
ORDER BY [Speaker and sessions Query].[Session#], [Speaker and sessions
Query].[S/M], [Speaker and sessions Query].NewLastName;
and the subreport qry:
SELECT DISTINCTROW Speakers.SpeakerID, Sessions.[Session#], [Speaker
Details].SessionID, [Speaker Details].[S/M], Speakers.NewFirstName,
Speakers.NewLastName, Speakers.NewPhone, Speakers.NewEmail
FROM Sessions INNER JOIN (Speakers INNER JOIN [Speaker Details] ON
Speakers.SpeakerID = [Speaker Details].SpeakerID) ON Sessions.SessionID =
[Speaker Details].SessionID
ORDER BY Sessions.[Session#], Speakers.NewLastName;
Thanks again for all of your help!
KARL DEWEY said:
Post the SQL of both queries.
--
KARL DEWEY
Build a little - Test a little
:
Hi Karl, thank you for your response.
I dont think I am doing it correctly. I took the main report qry
(speakerconfirmationQry) and added it into the subreport qry
(additionalspeakersqry) linked the two qrys on the reports links (child and
master links) which is session# and put the critiera of
<>[speakerconfirmationQry].[SpeakerID] on the additionalspeakersqry field
[speakerid] but it doesnt seem to be working. I am even getting dups,
triplets, quadrupliates of some of the data, depending on how many speakers
on a session. What am I doing wrong?
Thank you so much for your assistance!
Dayna
:
Use the query for the main report in the query for the sub report. Join on
the fields that are the link and use the main report query name field as
criteria like this --
Field Row SubReportNameField
Criteria <>[MainReportQuery].[NameField]
--
KARL DEWEY
Build a little - Test a little
:
HI, I have a report that includes a subreport. The main report has
information being sent to a speaker and the subreport includes all of the
speakers on the same panel. What I am trying to do is eliminate the speakers
name from the subreport when the main report is being sent to that person.
So if the main letter is addressed to John Doe and the speakers of the panel
are John Doe and Jane Doe only Janes name would appear on the subreport.
Any suggestions on how to best go about achieving this?
)