Hi there,
I'm trying to build a query to calculate a Delta Price but I need to remove from the scope [Y Orders with Pyramids], some references listed in a table [Delta Price Reference to excludes] bt using "starting with".
In the table I got some references like "ABC', "123" etc... and I'm basically trying to do a "Not Like ABC*" type of query, but linked to the exclusion table .
Any idea on how to do it?
My current query is the following:
does not work. I got the full list.
Hope you can help me.
Thank you !
I'm trying to build a query to calculate a Delta Price but I need to remove from the scope [Y Orders with Pyramids], some references listed in a table [Delta Price Reference to excludes] bt using "starting with".
In the table I got some references like "ABC', "123" etc... and I'm basically trying to do a "Not Like ABC*" type of query, but linked to the exclusion table .
Any idea on how to do it?
My current query is the following:
Code:
SELECT [Y Orders with Pyramids].[Reporting 1], [Y Orders with Pyramids].[Reporting 3], [Y Orders with Pyramids].Material, [Delta Price Reference Matching].[New reference], IIf(IsNull([New reference]),[Material],[New reference]) AS [Clean ref], Sum([Y Orders with Pyramids].[Market View =S=]) AS [SumOfMarket View =S=], Sum([Y Orders with Pyramids].Qty) AS SumOfQty, Sum([Market View =S=])/Sum([Qty]) AS [NSP Y]
FROM ([Y Orders with Pyramids] LEFT JOIN [Delta Price Reference Matching] ON [Y Orders with Pyramids].Material = [Delta Price Reference Matching].[Old reference]) LEFT JOIN [Delta Price Reference to excludes] ON [Y Orders with Pyramids].Material = [Delta Price Reference to excludes].Reference
WHERE ((([Y Orders with Pyramids].Month)<=[Select Y month]) AND (([Y Orders with Pyramids].Country)="Vietnam") AND (([Y Orders with Pyramids].[Reporting 2])="Transactional") AND (([Y Orders with Pyramids].[Salesteam / Order])<>"Intercompany") AND ([Y Orders with Pyramids].Material) Not in(select [Delta Price Reference to excludes].[Reference] from [Delta Price Reference to excludes] where [Delta Price Reference to excludes].[Reference] like [Delta Price Reference to excludes].[Reference] & "*"))
GROUP BY [Y Orders with Pyramids].[Reporting 1], [Y Orders with Pyramids].[Reporting 3], [Y Orders with Pyramids].Material, [Delta Price Reference Matching].[New reference], IIf(IsNull([New reference]),[Material],[New reference])
HAVING (((Sum([Y Orders with Pyramids].[Market View =S=]))<>0));
Code:
But the last part in WHERE:
AND ([Y Orders with Pyramids].Material) Not in(select [Delta Price Reference to excludes].[Reference] from [Delta Price Reference to excludes] where [Delta Price Reference to excludes].[Reference] like [Delta Price Reference to excludes].[Reference] & "*"))
Hope you can help me.
Thank you !