MS Access Query Results - Relationship Extension

Question

I have 4 tables: A, B, C and a linking table. I would like to know if it is possible to retrieve records from C by setting criteria in A, where there is a record in the linking table which links A and B, and a separate record in the linking table for B and C.

Here's a simplified image of the relationships:

I have tried setting up a query showing relevant fields from A, B and C, with criteria set in A. The linking table is present in the query. Running the query only returns the linked records from B. Is there something I should be putting in the criteria in the linked field of C? (I am doing this in design view for the query - my knowledge SQL is limited.)

.

SQL from Access:

SELECT Fruits.Fruit, Colour.Colour, Pests.Pest
FROM Pests RIGHT JOIN (Fruits RIGHT JOIN (Colour RIGHT JOIN [Linking Table] ON Colour.ID = [Linking Table].Colour) ON Fruits.ID = [Linking Table].Fruit) ON Pests.ID = [Linking Table].Pest
WHERE (((Pests.Pest)="Fly"));

Input: Fly

Output: Apple

Desired Output: Apple and Red

The linking table has a record linking the ID of fruit to pest, and a separate record linking the ID of fruit to colour.

Any assistance is greatly appreciated.

Answer 1

You're using your linking table for 2 kinds of links. This is a bad practice (normalization dictates that all columns in a table should be related, and the column pest has nothing to do with the column colour). Using one table to link color to fruit, and one to link pests to fruit would be a better plan.

You can still use this if you want to, but you will have to join in Linking Table twice (once for the fruit - pests relationship, once for the fruits - color relationship)

Try the following query:

SELECT Fruits.Fruit, Colour.Colour, Pests.Pest
FROM Pests
INNER JOIN [Linking Table] AS LT1 ON Pests.ID = LT1.Pest
INNER JOIN Fruits ON LT1.Fruit = Fruits.ID
INNER JOIN [Linking Table] AS LT2 ON Fruits.ID = LT2.Fruit
INNER JOIN Colour ON LT2.Colour = Colour.ID

This query goes from Pest to Linking table to Fruits then to a second instance of Linking table and then to Colour