Combining data from opposite ends of a 5 table chain

[Guest]

I have 5 tables set up as follows:

A-b-C-d-E

Tables b and d are linking tables because A and C have a many-to-many
relationship and so do C and E. I would like to set up a query whereby Access
will tell me which records of E are connected to any single record of A that
I specify.

If this is too abstract, A = students (e.g. Fred Bloggs), C = classes (e.g.
e.g. Maths Group B for a particular year of students) and E = teachers (e.g.
Mr Bloggs). I would like to know which teachers any particular student has,
and also - in the opposite direction - which students any particular teacher
has.

 

[jahoobob via AccessMonster.com]

I don't beleive your realtionships are stated correctly. You may have more
than one Maths Group B class being taught but they will be distinguished by
the time, room where they are being taught, and teacher. Mr. Bloggs may
teach 4 Maths Group B classes but not at the same time and place. Ms. Bleggs
may also teach Maths Group B it won't be the same class as any of Mr. Bloggs.
Ms. Bleggs may also teach 3 Maths Group A classes.

I would also have 5 tables (and these may be the same ones you have):
tblStudents
StudentID - PK
Name, etc

tblSubjects
SubjectID - PK
Description, etc.

tblTeachers
TeacherID - PK
Name, etc.

tblClasses
ClassID - PK
SubjectID - FK
TeacherID - FK
Time
Room

tblEnrollment
EnrollmentID - PK
ClassID - FK
StudentID - FK

You can then query tblTeachers, tbl Classes, tblEnrollemtn, and tblStudents
to get you associations.

 

[pietlinden]

If you create a query, add all the tables in order and link primary key
to foreign key across the relationships, then you can just show some
fields from A and E. No need to see any data in B, C, D at all. Say
you wanted all customers that purchased goods from a given supplier.

Customers--Invoices---InvoiceDetails---Products---Suppliers

If you use NWind, you can just copy the following SQL into a new query
(click SQL button)

SELECT DISTINCT Customers.CompanyName AS CustomerName,
Suppliers.CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID
= Products.SupplierID;

 

[pietlinden]

I have 5 tables set up as follows:

A-b-C-d-E

Tables b and d are linking tables because A and C have a many-to-many
relationship and so do C and E. I would like to set up a query whereby Access
will tell me which records of E are connected to any single record of A that
I specify.

If this is too abstract, A = students (e.g. Fred Bloggs), C = classes (e.g.
e.g. Maths Group B for a particular year of students) and E = teachers (e.g.
Mr Bloggs). I would like to know which teachers any particular student has,
and also - in the opposite direction - which students any particular teacher
has.

A = students
b = class roster
C = classes/section
d = "teaches class"
E = teacher

What's table d for? Can more than one teacher teach a single section
at a time?

 

[pietlinden]

If you create a query, add all the tables in order and link primary key
to foreign key across the relationships, then you can just show some
fields from A and E. No need to see any data in B, C, D at all. Say
you wanted all customers that purchased goods from a given supplier.

Customers--Invoices---InvoiceDetails---Products---Suppliers

If you use NWind, you can just copy the following SQL into a new query
(click SQL button)

SELECT DISTINCT Customers.CompanyName AS CustomerName,
Suppliers.CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID
= Products.SupplierID;