Consulta SQL

[gildus]

No manejo mucho access pero como SQL Standar podrias probarlo:

Código SQL:

SELECT 
    c.n 
FROM 
    contratos, 
    clientes,  
    (
        SELECT id_cliente, id_obra1 AS id_obra, n1 AS n FROM clientes UNION ALL 
        SELECT id_cliente, id_obra2 AS id_obra, n2 AS n FROM clientes  UNION ALL 
        SELECT id_cliente, id_obra3 AS id_obra, n3 AS n FROM clientes  UNION ALL 
        SELECT id_cliente, id_obra4 AS id_obra, n4 AS n FROM clientes
    ) AS c
 
WHERE 
    contratos.id_cliente = clientes.id_cliente AND      
    c.id_obra = contratos.obra_paciente AND 
    contratos.id_contrato=1 AND 
    contratos.id_cliente = 1  AND 
    contratos.obra_paciente = 22;
 

Saludos