Query "Not Alike"

[dudumomo]

Hi,

I have a product references table where I want to remove all the references starting by "ABC-"
The list to exclude is in another table.

It simple to exclude a direct match (Left Join + Is Null) but I have no idea on how to exclude not a direct match but "Starting With" match.

Any idea?

Thank you

[CJ_London]

You would need a query along these lines

UPDATE tblProdRef, tblOther
SET tblProdRef.Reference=""
WHERE tblProdRef.Reference like tblOther.Reference & "*"

If this is not what you require, provide some example data for both tables and the required end result

[dudumomo]

Thanks for your feedback.

I'm having some difficulties (Quite new to SQL).

Here is what I'm using now.

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"))
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 ((([Y Orders with Pyramids].Material) Not Like [Delta Price Reference To Excludes].[Reference]) AND ((Sum([Y Orders with Pyramids].[Market View =S=]))<>0));

But it doesn't work (Syntax error). If I remove the HAVING ([Y Orders with Pyramids].Material) Not Like [Delta Price Reference To Excludes].[Reference]) it works well.
Not sure where to put it then.

Thanks for your help !

[CJ_London]

you wont be able to do an update with a group by query, try changing it back to an 'normal' query