2 tables oif similar data, how do I lookup on a form??

[JR Hester]

Access XP on WinXP.

In a quandry, hope someone has a practical suggestion.

developing a training database including tables for participants, classes,
sessions, rooms and ExternalInstructors. Current dilemma deals with
Participant and ExternalInstructor tables and a CreateSessions form.
Sometimes we contract outside vendors to supply instructors for specific
class sessions. Most sessions are led by members of our staff. ( Important
note all particiapnts are staff members).
Problem:
I have envisioned and planned to have a single field on my data entry form
to record the session Instructor. This enables a report showing the
instructor for each session. I do NOT want external instructors added to our
participant table and it does not make sense to add participants who are also
instructors to the ExternalInstructors table. Is there a way of using one
field on the form to lookup values from both tables? How would i do this?

Of course I can palce two fields on the data entry form, one for external
instructor and one for internal(participants). This is an easy option and may
be my best or only solution. How can I prevent someone from choosing an
option in both of these fields? In my scenario, I can't forsee having both an
internal and External instructor for the same class session. If this is the
approach to take, How should I proceed with the query/report to pull data
from whichever table is applicable for each session?

Thanks for your thoughts suggestions

 

[Ken Sheridan]

You can reference both tables by means of a UNION query as the RowSource
property of a combo box on the form bound to the Instructor field, e.g.

SELECT (FirstName + " ") & LastName
FROM Participants
UNION ALL
SELECT (FirstName + " ") & LastName
FROM ExternalInstructors
ORDER BY LastName, FirstName;

This does mean that the Instructor field would have to be of Text data type,
however, containing the concatenated name. This is not very satisfactory as
names can be duplicated. Normally a numeric foreign key would be used to
overcome this of course, referencing a numeric primary key of the referenced
table, often an autonumber. This is not possible when referencing two
tables, though, as the key values will almost certainly be duplicated using a
standard incrementing autonumber. Even random autonumber values are only
unique within each table. A Replication ID is globally unique, but these are
really intended for synchronizing replicated databases.

One option would be to have a text foreign key, InstructorID say, comprised
of a code letter, P or E say, followed by the value of an autonumber key to
differentiate between participant and external instructors, but also
differentiating any two or more instructors of the same name. The query for
the combo box's RowSource would then be:

SELECT "P" & ParticipantID, (Firstname + " ") & LastName,
LastName, FirstName
FROM Participants
UNION ALL
SELECT "E" & ExternalInstructorID, (Firstname + " ") & LastName,
LastName, FirstName
FROM ExternalInstructors
ORDER BY LastName, FirstName;

Note that, unlike in a normal SELECT query, in a UNION query it is necessary
to return the LastName and FirstName columns independently as well as as a
concatenated string expression in order that the result set can be ordered by
the values in these columns. The combo box's other properties would be as
follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first column, so only the name shows)

Referencing two tables in this way does have the drawback, however, that
referential integrity between the referenced and referencing tables cannot be
enforced.

In a report you'd include a similar UNION query in the report's underlying
query, but without ordering the result set:

SELECT "P" & ParticipantID AS InstructorID,
(FirstName + " ") & LastName AS FullName,
FROM Participants
UNION ALL
SELECT "E" & ExternalInstructorID,
(FirstName + " ") & LastName,
FROM ExternalInstructors;

joining this query to the Sessions table on the InstructorID columns and
returning the FullName column in the report as the instructor for the session.

If you were to have two or more instructors of the same name you'd have to
include some other column to distinguish them, both in the combo box's
RowSource and in the report. This is not always easy; I was not long ago at
a clinic where two patients with exactly the same names and dates of birth
attended on the same day! I also once worked in a department with two people
with identical names (different job titles fortunately).

Ken Sheridan
Stafford, England

 

[JR Hester]

Thanks for your quick response and the pros and cons of the suggestions. I
wil be implementing these ideas into my database next week.

Thanks for your participation in this forum.

 

[Bob Quintal]

Access XP on WinXP.

In a quandry, hope someone has a practical suggestion.

developing a training database including tables for participants,
classes, sessions, rooms and ExternalInstructors. Current dilemma
deals with Participant and ExternalInstructor tables and a
CreateSessions form. Sometimes we contract outside vendors to
supply instructors for specific class sessions. Most sessions are
led by members of our staff. ( Important note all particiapnts are
staff members). Problem:
I have envisioned and planned to have a single field on my data
entry form to record the session Instructor. This enables a report
showing the instructor for each session. I do NOT want external
instructors added to our participant table and it does not make
sense to add participants who are also instructors to the
ExternalInstructors table. Is there a way of using one field on
the form to lookup values from both tables? How would i do this?

There are a couple of methods, but first let us talk the structure
of the database. You are missing one piece of the puzzle, and thats
instructor certifications: "Is John Doe a trained instructor in this
subject?". "Is John Doe capable of teaching three different
subjects?". I ask because some training requires certified
instructors.

That info does not belong in the participants table, because the
instructor may teach more than one course, therefore you need one
more table, to hold info about the staff instructors.
You only need to put the instructor's employee ID or external ID,
not all the other data in the table, a column that contains a code
to indicate eXternal or iNternal, and the course ID they are
certified for. If they teach 3 courses, the instructor's ID will
appear in the instructors table 3 times. You relate this table to
both your participants and external instructors tables in a query.
using left|right joins and iif() statements based on the
iNternal/eXternal flag.

Then you use the query as a row source for your combobox.

Of course I can palce two fields on the data entry form, one for
external instructor and one for internal(participants). This is an
easy option and may be my best or only solution. How can I prevent
someone from choosing an option in both of these fields? In my
scenario, I can't forsee having both an internal and External
instructor for the same class session. If this is the approach to
take, How should I proceed with the query/report to pull data from
whichever table is applicable for each session?

The other option is to create a union query of external instructrors
and internal instructors. It will work, but you'd be missing the
answers to the important questions I highlighted above.

Thanks for your thoughts suggestions

 

[Ken Sheridan]

Just noticed that one of the queries contained extra commas:

SELECT "P" & ParticipantID AS InstructorID,
(FirstName + " ") & LastName AS FullName,
FROM Participants
UNION ALL
SELECT "E" & ExternalInstructorID,
(FirstName + " ") & LastName,
FROM ExternalInstructors;

should have read:

SELECT "P" & ParticipantID AS InstructorID,
(FirstName + " ") & LastName AS FullName
FROM Participants
UNION ALL
SELECT "E" & ExternalInstructorID,
(FirstName + " ") & LastName
FROM ExternalInstructors;

Ken Sheridan
Stafford, England