Connecting to the Same Table Twice

[Joy M]

HI -

This database keeps track of firefighters who are taking courses at a
training school. I will give you a 'bare bones' description of the
situation and my problem.

The question I have concerns the department table, i.e. the fire department
name table. The department table looks like this:
DepartmentID
DepartmentName

The student table contains firefighter names and home departments. It looks
something like this:
StudentID
FirstName
LastName
HomeDepartment (foreign key to department table). The home department is a
lookup field.

There is also a student course registration table, where students enroll in
courses. The course can be paid for by a sponsoring department.
For example, firefighter X has department A as his home department, but
department F pays for this course. So department F goes on the student's
course certificate (which he receives when he completes the course).

The student course registration table looks something like this
StudentID
CourseID
Fees
Attendance
Mark
SponsoringDepartment (foreign key to department table)

My problem is when I print the student course certificates.. .I want to have
the student name and the sponsoring department name (not the ID) on the
certificate.

I don't know if this is possible because my query uses information from the
student table, the student course registration table and the department
table.
If I use SponsoringDepartment on the report, I only get the DepartmentID,
not the DepartmentName. I don't see how I can use the DepartmentName
because it is in a loop - on one hand it contains the DepartmentName of the
HomeDepartment, in the other it contains the DepartmentName of the
SponsoringDepartment.

Some ideas:
1 Duplicating the department table - but that creates a maintenance problem
2 Instead of storing the DepartmentID in HomeDepartment and
SponsoringDepartment, store the DepartmentName itself, which is considerably
longer. But then I would have it for the certificate.

I hope someone can give me some guidance. I am looking forward to hearing
your thoughts.

Thanks!

Joy

 

[John Vinson]

If I use SponsoringDepartment on the report, I only get the DepartmentID,
not the DepartmentName. I don't see how I can use the DepartmentName
because it is in a loop - on one hand it contains the DepartmentName of the
HomeDepartment, in the other it contains the DepartmentName of the
SponsoringDepartment.

You're another victim of Microsoft's misleading, misdesigned, and
infuriating Lookup Wizard misfeature. (No... I don't like it much).

Your table DOES NOT contain the department name. It appears to, but it
actually contains the DepartmentID, concealed from view by the Lookup.
It's not really there!

What you can do is create a Query, just as your subject line suggests,
connecting to the same table twice. Add your Firefighters table to the
query grid, then add the department table TWICE - Access will alias
the second instance by appending _1 to its name.

Join the first instance to the firefighter's home department; join the
second instance to the sponsoring department.

Select the two department names from the joined tables for your
report.

John W. Vinson[MVP]

 

[Joy M]

Thanks, John! Brilliant suggestion, worked like a charm.

Joy

 

[John Vinson]

Thanks, John! Brilliant suggestion, worked like a charm.

Joy

Glad to help, Joy. Give my regards (and respect and thanks) to a
firefighter or two!

John W. Vinson[MVP]