Select como critério para consulta subselect

Olá preciso executar uma sub consulta e que tem como critério de pesquisa um retorno da primeira consulta e que esta consulta executa várias vezes.
EX:
Tenho o Produto que sofreu alteração no seu fornecimento (No exemplo abaixo um KIT “AK0174” que é fornecido desmembrado):


Consegui executa uma Select que retorna



SELECT [Consulta KAC].[Part Number], [Replacement KAC].[Sub Part Number] as [Sub Part Number 1], (SELECT [Replacement KAC].[Sub Part Number] FROM [Replacement KAC]
Where
(SELECT
[Replacement KAC].[Sub Part Number]
FROM [Consulta KAC]
LEFt JOIN
[Replacement KAC] ON
[Consulta KAC].[Part Number] = [Replacement KAC].[Part Number]
WHERE
((([Replacement KAC].[Sub Part Number]) Is Not Null))) = [Replacement KAC].[Part Number]) AS [Sub Part Number 2]
FROM [Consulta KAC] LEFT JOIN [Replacement KAC] ON [Consulta KAC].[Part Number] = [Replacement KAC].[Part Number];

Mas quando executo a procura a partir do item Original (AK0174) retorna um erro:


É possível realizar esta consulta?