Query exclude references_from_table starting with

[dudumomo]

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:

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));

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] & "*"))

does not work. I got the full list.

Hope you can help me.

Thank you !

 

[Micron]

If memory serves, Not IN ( will not work with a statement. I seem to recall trying to use the IN clause in this way before, only to find that it only works with a separated list of values. If your select sub statement only returns one value, try using <>. If it returns more than one record as I suspect it does, try basing your query on a filter query.

 

[xenou]

You've already got [Delta Price Reference to excludes] in your join clause:

LEFT JOIN [Delta Price Reference to excludes] ON [Y Orders with Pyramids].Material = [Delta Price Reference to excludes].Reference

It shouldn't be needed in both the join clause and the where clause (otherwise its seems it would be either redundant or contradictory.

What's the join supposed to be doing? Maybe you can fix everything right there.

Possibly:

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]) 
[B]		[COLOR="#FF0000"]LEFT JOIN [Delta Price Reference to excludes] 
		ON [Y Orders with Pyramids].Material LIKE [Delta Price Reference to excludes].Reference & '*'[/COLOR][/B]

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") 
[B]	[COLOR="#FF0000"]	AND 
		(([Delta Price Reference to excludes]) IS NULL)[/COLOR] [/B]
	)	

		
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))

;