MS Access Query Results - Relationship Extension -

i have 4 tables: a, b, c , linking table. know if possible retrieve records c setting criteria in a, there record in linking table links , b, , separate record in linking table b , c.

here's simplified image of relationships:

i have tried setting query showing relevant fields a, b , c, criteria set in a. linking table present in query. running query returns linked records b. there should putting in criteria in linked field of c? (i doing in design view query - knowledge sql limited.)

.

sql access:

select fruits.fruit, colour.colour, pests.pest 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 (((pests.pest)="fly")); 

input: fly

output: apple

desired output: apple , red

the linking table has record linking id of fruit pest, , separate record linking id of fruit colour.

any assistance appreciated.

you're using linking table 2 kinds of links. bad practice (normalization dictates columns in table should related, , column pest has nothing column colour). using 1 table link color fruit, , 1 link pests fruit better plan.

you can still use if want to, have join in linking table twice (once fruit - pests relationship, once fruits - color relationship)

try following query:

select fruits.fruit, colour.colour, pests.pest pests inner join [linking table] lt1 on pests.id = lt1.pest inner join fruits on lt1.fruit = fruits.id inner join [linking table] lt2 on fruits.id = lt2.fruit inner join colour on lt2.colour = colour.id 

this query goes pest linking table fruits second instance of linking table , colour