proper syntax to place table data onto report

[JR Hester]

AccessXP running on WinXP.

I am tackling another stumbling block along my journey through Access, one
hurdle at a time. I still work primarily in the drag and drop process of
report and form design, so I do not have a full understanding of the VBA
coding syntax and procedures.

I need to add one more piece of data to a working report, rptClassSignIn.
Report is based on query named qrySessionParticipants, sql code included
below.

SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor
FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID =
tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN
(tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));

I need to add the instructor NAME to my report in the header section. At
present I am working on including internal instructors. Internal instructors
exist in the participants table, not in a separate instructors table. I chose
this method to eliminate duplicate data in database. I have an
ExternalInstructor table for data relating to those instructors who are not
participants(employees of our organization).

Hopefully enough background, Now to the problem at hand. I can’t think
though the process of getting the first and last names from the participant
table that matches the instructorID in the query the report is based on. This
may be one of those cases where only VBA instruction will work. So here is
what I think I want in my report field:

Where qrySessionParticipant!InstructorID=tblParticipant!participantID, print
tblParticipant!EmpFname &†“& tblParticipant!EmpLname

Of course, when I put this line into my control source field and run the
report, I get a dialog box expecting input for this variable. What is the
correct syntax for my request? Am I any where near close? Or would I be
better off to just bite the bullet and add an Interanl instructor table and
duplicate the data for these few personnel from the participants table?

Included basic tables and fields below
Tables:
Participants>> ID, Fname, Lname, DepartmentID,
ParticpantTransactions>> TrxID, participantID, SessionID, enrolled,
attended, completed, grade, departmentID
Sessions>> ID, ClassID, date, time, InstructorID
Classes>> ID,name, description
Department>>ID, name, manager, etc
ExternalInstructors>> ID, Lname, Fname, Contact#, etc

Thanks for any guidance you may offer.

 

[Duane Hookom]

It looks like you have a single field in the Sessions table to store the
InstructorID which could relate to an ID from the Participants table or an ID
from the ExternalInstructors table. Is this correct? What happens if a
participant has the same ID value as an ExternalInstructor?

You could create a union query like:
SELECT ID, FName, LName
FROM Participants
UNION ALL
SELECT ID, FName,LName
FROM ExternalInstructors;

Use this query rather than ExternalInstructors in your report's record source.
--
Duane Hookom
Microsoft Access MVP

AccessXP running on WinXP.

I am tackling another stumbling block along my journey through Access, one
hurdle at a time. I still work primarily in the drag and drop process of
report and form design, so I do not have a full understanding of the VBA
coding syntax and procedures.

I need to add one more piece of data to a working report, rptClassSignIn.
Report is based on query named qrySessionParticipants, sql code included
below.

SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor
FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID =
tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN
(tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));

I need to add the instructor NAME to my report in the header section. At
present I am working on including internal instructors. Internal instructors
exist in the participants table, not in a separate instructors table. I chose
this method to eliminate duplicate data in database. I have an
ExternalInstructor table for data relating to those instructors who are not
participants(employees of our organization).

Hopefully enough background, Now to the problem at hand. I can’t think
though the process of getting the first and last names from the participant
table that matches the instructorID in the query the report is based on. This
may be one of those cases where only VBA instruction will work. So here is
what I think I want in my report field:

Where qrySessionParticipant!InstructorID=tblParticipant!participantID, print
tblParticipant!EmpFname &†“& tblParticipant!EmpLname

Of course, when I put this line into my control source field and run the
report, I get a dialog box expecting input for this variable. What is the
correct syntax for my request? Am I any where near close? Or would I be
better off to just bite the bullet and add an Interanl instructor table and
duplicate the data for these few personnel from the participants table?

Included basic tables and fields below
Tables:
Participants>> ID, Fname, Lname, DepartmentID,
ParticpantTransactions>> TrxID, participantID, SessionID, enrolled,
attended, completed, grade, departmentID
Sessions>> ID, ClassID, date, time, InstructorID
Classes>> ID,name, description
Department>>ID, name, manager, etc
ExternalInstructors>> ID, Lname, Fname, Contact#, etc

Thanks for any guidance you may offer.

 

[JR Hester]

I do have two fields in the sessions table, One for internal instructors
linking back to the participants table and another for External Instructors
linked to the ExternalInstructor table.

Here is my dilemma:
The report is based on the Sessionparticipants Query which has, among other
data, the following
ParticipantID which can supply fname and Lname of participants from the
participants table
INstructorID which needs to supply Fname and Lname from particiapnts table

?? How do instruct either the query or teh report to populate a field with
fname from the participant table where the tblParticpant!participantID =
QrySessionParticipants!InstructorID ?? What I am currently getting by just
asking for Fname and Lname is the Fname and Lanme of the first particpant in
the query, not the fname and lname of the INSTRUCTOR.

Thanks for your interest and response and any additioanly guidance you can
offer.

It looks like you have a single field in the Sessions table to store the
InstructorID which could relate to an ID from the Participants table or an ID
from the ExternalInstructors table. Is this correct? What happens if a
participant has the same ID value as an ExternalInstructor?

You could create a union query like:
SELECT ID, FName, LName
FROM Participants
UNION ALL
SELECT ID, FName,LName
FROM ExternalInstructors;

Use this query rather than ExternalInstructors in your report's record source.
--
Duane Hookom
Microsoft Access MVP

AccessXP running on WinXP.

I am tackling another stumbling block along my journey through Access, one
hurdle at a time. I still work primarily in the drag and drop process of
report and form design, so I do not have a full understanding of the VBA
coding syntax and procedures.

I need to add one more piece of data to a working report, rptClassSignIn.
Report is based on query named qrySessionParticipants, sql code included
below.

SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor
FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID =
tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN
(tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));

I need to add the instructor NAME to my report in the header section. At
present I am working on including internal instructors. Internal instructors
exist in the participants table, not in a separate instructors table. I chose
this method to eliminate duplicate data in database. I have an
ExternalInstructor table for data relating to those instructors who are not
participants(employees of our organization).

Hopefully enough background, Now to the problem at hand. I can’t think
though the process of getting the first and last names from the participant
table that matches the instructorID in the query the report is based on. This
may be one of those cases where only VBA instruction will work. So here is
what I think I want in my report field:

Where qrySessionParticipant!InstructorID=tblParticipant!participantID, print
tblParticipant!EmpFname &†“& tblParticipant!EmpLname

Of course, when I put this line into my control source field and run the
report, I get a dialog box expecting input for this variable. What is the
correct syntax for my request? Am I any where near close? Or would I be
better off to just bite the bullet and add an Interanl instructor table and
duplicate the data for these few personnel from the participants table?

Included basic tables and fields below
Tables:
Participants>> ID, Fname, Lname, DepartmentID,
ParticpantTransactions>> TrxID, participantID, SessionID, enrolled,
attended, completed, grade, departmentID
Sessions>> ID, ClassID, date, time, InstructorID
Classes>> ID,name, description
Department>>ID, name, manager, etc
ExternalInstructors>> ID, Lname, Fname, Contact#, etc

Thanks for any guidance you may offer.

 

[Duane Hookom]

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

--
Duane Hookom
Microsoft Access MVP

I do have two fields in the sessions table, One for internal instructors
linking back to the participants table and another for External Instructors
linked to the ExternalInstructor table.

Here is my dilemma:
The report is based on the Sessionparticipants Query which has, among other
data, the following
ParticipantID which can supply fname and Lname of participants from the
participants table
INstructorID which needs to supply Fname and Lname from particiapnts table

?? How do instruct either the query or teh report to populate a field with
fname from the participant table where the tblParticpant!participantID =
QrySessionParticipants!InstructorID ?? What I am currently getting by just
asking for Fname and Lname is the Fname and Lanme of the first particpant in
the query, not the fname and lname of the INSTRUCTOR.

Thanks for your interest and response and any additioanly guidance you can
offer.

It looks like you have a single field in the Sessions table to store the
InstructorID which could relate to an ID from the Participants table or an ID
from the ExternalInstructors table. Is this correct? What happens if a
participant has the same ID value as an ExternalInstructor?

You could create a union query like:
SELECT ID, FName, LName
FROM Participants
UNION ALL
SELECT ID, FName,LName
FROM ExternalInstructors;

Use this query rather than ExternalInstructors in your report's record source.
--
Duane Hookom
Microsoft Access MVP

AccessXP running on WinXP.

I am tackling another stumbling block along my journey through Access, one
hurdle at a time. I still work primarily in the drag and drop process of
report and form design, so I do not have a full understanding of the VBA
coding syntax and procedures.

I need to add one more piece of data to a working report, rptClassSignIn.
Report is based on query named qrySessionParticipants, sql code included
below.

SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor
FROM (tblClasses INNER JOIN tblSessions ON tblClasses.ClassID =
tblSessions.ClassID) INNER JOIN (tblParticipants INNER JOIN
(tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));

I need to add the instructor NAME to my report in the header section. At
present I am working on including internal instructors. Internal instructors
exist in the participants table, not in a separate instructors table. I chose
this method to eliminate duplicate data in database. I have an
ExternalInstructor table for data relating to those instructors who are not
participants(employees of our organization).

Hopefully enough background, Now to the problem at hand. I can’t think
though the process of getting the first and last names from the participant
table that matches the instructorID in the query the report is based on. This
may be one of those cases where only VBA instruction will work. So here is
what I think I want in my report field:

Where qrySessionParticipant!InstructorID=tblParticipant!participantID, print
tblParticipant!EmpFname &†“& tblParticipant!EmpLname

Of course, when I put this line into my control source field and run the
report, I get a dialog box expecting input for this variable. What is the
correct syntax for my request? Am I any where near close? Or would I be
better off to just bite the bullet and add an Interanl instructor table and
duplicate the data for these few personnel from the participants table?

Included basic tables and fields below
Tables:
Participants>> ID, Fname, Lname, DepartmentID,
ParticpantTransactions>> TrxID, participantID, SessionID, enrolled,
attended, completed, grade, departmentID
Sessions>> ID, ClassID, date, time, InstructorID
Classes>> ID,name, description
Department>>ID, name, manager, etc
ExternalInstructors>> ID, Lname, Fname, Contact#, etc

Thanks for any guidance you may offer.

 

[JR Hester]

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

 

[Duane Hookom]

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[JR Hester]

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[Duane Hookom]

I assume you have some value in the external instructor field of some
records. You seem to be suggesting that none of these values match the
primary key value in your external instructors table. If this is correct then
you have a misunderstanding in your table structures and/or input screens.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

:

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[JR Hester]

Let's see if I can better relate the situation. What I really need is EVERY
record that matches the date/time criteria, then report fname/lname data for
InternalInstructor and/or External Instructor.

Your assumption is correct, there are several records in the sessions table
with an entry in the External INstructor field and NOTHING in the
InternalInstructor field.

I am NOT suggesting that entries in the ExtInstructor field do not match the
ExtInstructor ID. Quite the opposite is true, each entry in ExtInstructor
field in Sessions table matches a records ID in ExtInstructor table. The
excerpt below indicates ext Instructor #3 in Session id 112, all other
reocords have internal instructor references to 7785 or 6749.

SessionID Class StrtDate StartTime EndTime MaxParticipants Instructor location ExternalInstructor
112 Outlook, Beginning 1/25/2005 8:30 16:30 10 3 2
113 Word, Beginner 1/16/2005 8:30 16:30 10 7785 3
114 Excel, Beginners 1/13/2005 8:00 16:00 10 6749 3
115 Word, Beginner 11/29/2004 8:00 16:30 10 7785 3

My query is based on date and start time. When I attempt to query on 1/25/05
at 8:30( session 112) no records are returned. This is the only record
currently with an external instructor link. I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, the join between sessions & external
INstructors to option #2. This works for internal instructors but not for
external instructors

Querying any date/time pair that has a valid internal instructor link
returns expected records. It appears that the query first slects only those
records with data in the internal instructor field and then searches that
subset for those records with values in teh ExternalInstructor field.

What I really need is EVERY record that matches the date/time criteria, then
report fname/lname data for InternalInstructor and/or External Instructor


Thanks again for your time and patience

I assume you have some value in the external instructor field of some
records. You seem to be suggesting that none of these values match the
primary key value in your external instructors table. If this is correct then
you have a misunderstanding in your table structures and/or input screens.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

:

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[Duane Hookom]

Did you set the join type of the internal instructor to the participant table
to include all records from the tblSessions?
--
Duane Hookom
Microsoft Access MVP

Let's see if I can better relate the situation. What I really need is EVERY
record that matches the date/time criteria, then report fname/lname data for
InternalInstructor and/or External Instructor.

Your assumption is correct, there are several records in the sessions table
with an entry in the External INstructor field and NOTHING in the
InternalInstructor field.

I am NOT suggesting that entries in the ExtInstructor field do not match the
ExtInstructor ID. Quite the opposite is true, each entry in ExtInstructor
field in Sessions table matches a records ID in ExtInstructor table. The
excerpt below indicates ext Instructor #3 in Session id 112, all other
reocords have internal instructor references to 7785 or 6749.

SessionID Class StrtDate StartTime EndTime MaxParticipants Instructor location ExternalInstructor
112 Outlook, Beginning 1/25/2005 8:30 16:30 10 3 2
113 Word, Beginner 1/16/2005 8:30 16:30 10 7785 3
114 Excel, Beginners 1/13/2005 8:00 16:00 10 6749 3
115 Word, Beginner 11/29/2004 8:00 16:30 10 7785 3

My query is based on date and start time. When I attempt to query on 1/25/05
at 8:30( session 112) no records are returned. This is the only record
currently with an external instructor link. I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, the join between sessions & external
INstructors to option #2. This works for internal instructors but not for
external instructors

Querying any date/time pair that has a valid internal instructor link
returns expected records. It appears that the query first slects only those
records with data in the internal instructor field and then searches that
subset for those records with values in teh ExternalInstructor field.

What I really need is EVERY record that matches the date/time criteria, then
report fname/lname data for InternalInstructor and/or External Instructor


Thanks again for your time and patience

I assume you have some value in the external instructor field of some
records. You seem to be suggesting that none of these values match the
primary key value in your external instructors table. If this is correct then
you have a misunderstanding in your table structures and/or input screens.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

:

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

:

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[JR Hester]

I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, Only include records where joined fields from
boith tables are equal.

I have not had any success with setting options for ALL records from either
direction.

Did you set the join type of the internal instructor to the participant table
to include all records from the tblSessions?
--
Duane Hookom
Microsoft Access MVP

Let's see if I can better relate the situation. What I really need is EVERY
record that matches the date/time criteria, then report fname/lname data for
InternalInstructor and/or External Instructor.

Your assumption is correct, there are several records in the sessions table
with an entry in the External INstructor field and NOTHING in the
InternalInstructor field.

I am NOT suggesting that entries in the ExtInstructor field do not match the
ExtInstructor ID. Quite the opposite is true, each entry in ExtInstructor
field in Sessions table matches a records ID in ExtInstructor table. The
excerpt below indicates ext Instructor #3 in Session id 112, all other
reocords have internal instructor references to 7785 or 6749.

SessionID Class StrtDate StartTime EndTime MaxParticipants Instructor location ExternalInstructor
112 Outlook, Beginning 1/25/2005 8:30 16:30 10 3 2
113 Word, Beginner 1/16/2005 8:30 16:30 10 7785 3
114 Excel, Beginners 1/13/2005 8:00 16:00 10 6749 3
115 Word, Beginner 11/29/2004 8:00 16:30 10 7785 3

My query is based on date and start time. When I attempt to query on 1/25/05
at 8:30( session 112) no records are returned. This is the only record
currently with an external instructor link. I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, the join between sessions & external
INstructors to option #2. This works for internal instructors but not for
external instructors

Querying any date/time pair that has a valid internal instructor link
returns expected records. It appears that the query first slects only those
records with data in the internal instructor field and then searches that
subset for those records with values in teh ExternalInstructor field.

What I really need is EVERY record that matches the date/time criteria, then
report fname/lname data for InternalInstructor and/or External Instructor


Thanks again for your time and patience

I assume you have some value in the external instructor field of some
records. You seem to be suggesting that none of these values match the
primary key value in your external instructors table. If this is correct then
you have a misunderstanding in your table structures and/or input screens.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

:

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

:

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.

 

[Duane Hookom]

You can't expect to display records with external instructors if you only
show records that are related to internal instructors. You must find the
proper syntax to include all records from the sessions and only matching
records from internal and external.

--
Duane Hookom
Microsoft Access MVP

I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, Only include records where joined fields from
boith tables are equal.

I have not had any success with setting options for ALL records from either
direction.

Did you set the join type of the internal instructor to the participant table
to include all records from the tblSessions?
--
Duane Hookom
Microsoft Access MVP

Let's see if I can better relate the situation. What I really need is EVERY
record that matches the date/time criteria, then report fname/lname data for
InternalInstructor and/or External Instructor.

Your assumption is correct, there are several records in the sessions table
with an entry in the External INstructor field and NOTHING in the
InternalInstructor field.

I am NOT suggesting that entries in the ExtInstructor field do not match the
ExtInstructor ID. Quite the opposite is true, each entry in ExtInstructor
field in Sessions table matches a records ID in ExtInstructor table. The
excerpt below indicates ext Instructor #3 in Session id 112, all other
reocords have internal instructor references to 7785 or 6749.

SessionID Class StrtDate StartTime EndTime MaxParticipants Instructor location ExternalInstructor
112 Outlook, Beginning 1/25/2005 8:30 16:30 10 3 2
113 Word, Beginner 1/16/2005 8:30 16:30 10 7785 3
114 Excel, Beginners 1/13/2005 8:00 16:00 10 6749 3
115 Word, Beginner 11/29/2004 8:00 16:30 10 7785 3

My query is based on date and start time. When I attempt to query on 1/25/05
at 8:30( session 112) no records are returned. This is the only record
currently with an external instructor link. I currently have the query join
between session & participants( the second instance looking for internal
instructor) set to option #1, the join between sessions & external
INstructors to option #2. This works for internal instructors but not for
external instructors

Querying any date/time pair that has a valid internal instructor link
returns expected records. It appears that the query first slects only those
records with data in the internal instructor field and then searches that
subset for those records with values in teh ExternalInstructor field.

What I really need is EVERY record that matches the date/time criteria, then
report fname/lname data for InternalInstructor and/or External Instructor


Thanks again for your time and patience


:

I assume you have some value in the external instructor field of some
records. You seem to be suggesting that none of these values match the
primary key value in your external instructors table. If this is correct then
you have a misunderstanding in your table structures and/or input screens.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane
I did find the join properties, I used option #2 and then works OK for those
records having an internal instructor and no External Instructor. However for
records with no internal instructor but an External instructor, no records
are returned by the Query.

It seems that I have run into that aggravation with relational tables again.
The only option I can see is to develop two separate reports and their
queries, one for internal instructors and one for external instructors. Then
operator wil have to predetermine whether the session has an Internal or
External instructor. Or am I missing some simpler method of accomplishing
this data recovery for reporting?

:

You should be able to double-click some join lines in the query design to set
the join properties. You want to select all records from one of your tables.
--
Duane Hookom
Microsoft Access MVP


:

Thanks for your patience and direction. The first part of my report field is
populating just as you anticiapted it would. That worked quite easily once I
got past trying to force it to work and just let it flow.

Worked so well that I thought it would work for the second part of my report
field. I am attempting to place 4 query fields into one report field.
Succeeded in gettting Particiapnt fname and lname. Now I want to add
ExtInstructor.Fname and ExtInstructor.Lname to the same field. In my source
table, both Instructor and ExternalInstructor should never be populated for
the same record, only one or the other.

I added the ExtInstructor table to my query, linked the ExtInstructorID in
ExtInstructor table to the ExtInstructor field in my sessions table. When I
run this query it returns no results. However if I remove the link between
ExtInstructor and Sessions tables then it returns results. I assume that once
teh link is established that puts another criteria on the query, and if only
those records that have a the ExtInstructor field populated will be returned.

Is this a vaild assumption? If yes, then how do I get an instructor from the
ExtInstructor table on my report without having two separate reports?

Here is the SQL of the query with the ?link? active.
SELECT tblSessions.StrtDate, tblSessions.StartTime, tblSessions.SessionID,
tblParticipantTrx.*, tblParticipantTrx.TrxID, tblParticipants.*,
tblClasses.*, tblDept.DeptName, tblSessions.Instructor,
tblParticipants_1.EmpFname, tblParticipants_1.EmpLname,
tblInstructorsExternal.InstrNameFirst, tblInstructorsExternal.InstrNameLast
FROM ((tblClasses INNER JOIN (tblSessions INNER JOIN tblParticipants AS
tblParticipants_1 ON tblSessions.Instructor = tblParticipants_1.EmpID) ON
tblClasses.ClassID = tblSessions.ClassID) INNER JOIN (tblParticipants INNER
JOIN (tblParticipantTrx INNER JOIN tblDept ON tblParticipantTrx.EmpDept =
tblDept.DeptID) ON tblParticipants.EmpID = tblParticipantTrx.EmpID) ON
tblSessions.SessionID = tblParticipantTrx.SessionID) INNER JOIN
tblInstructorsExternal ON tblSessions.ExternalInstructor =
tblInstructorsExternal.InstrID
WHERE (((tblSessions.StrtDate)=[date]) AND ((tblSessions.StartTime)=[time]));


Thanks again for your time & suggestions

:

You should just join tblParticipant into the query and join the appropriate
fields. This would be the second time tblParticipant was joined to the query.