Filter query

Question

Thanks for taking the time to read my question.

I want to filter out of my results where PlantCode = "41C" AND FormulaCode = "10898395" But I want to see the rest of the data.

My query below is filtering out ALL PlantCode = "41C"  regardless of FormulaCode. If I put in a filter on Just FormulaCode, I get the rest of the results.

SELECT tblXC1Header.XC1HeaderID, tblXC1Header.XC1Header, tblXC1Header.XC1HeaderPlant, tblXC1Header.XC1HeaderFormulaCode, tblXC1Header.XC1HeaderDescription, tblXC1Header.XC1HeaderBatchSize, tblXC1Header.XC1HeaderSpeciesCode, tblXC1Header.XC1HeaderNumIngr, tblXC1Header.XC1HeaderStructureType, tblXC1Header.XC1HeaderRoutingTemplate
FROM tblXC1Header
WHERE (((tblXC1Header.XC1HeaderPlant)<>"41c") AND ((tblXC1Header.XC1HeaderFormulaCode)<>"10898395"));

All my fields in tblXC1Header are Text format

I am quite baffled as I'm quite sure this is how the query should be made...

Any help would be great.

Thanks,
Brad

https://support.office.com/en-sg/article/Examples-of-query-criteria-08029ed5-39f1-4900-88e3-ab473b16d94e

Answer 1

SELECT
    tblXC1Header.XC1HeaderID,
    tblXC1Header.XC1Header,
    tblXC1Header.XC1HeaderPlant, 
    tblXC1Header.XC1HeaderFormulaCode, 
    tblXC1Header.XC1HeaderDescription, 
    tblXC1Header.XC1HeaderBatchSize, 
    tblXC1Header.XC1HeaderSpeciesCode, 
    tblXC1Header.XC1HeaderNumIngr, 
    tblXC1Header.XC1HeaderStructureType, 
    tblXC1Header.XC1HeaderRoutingTemplate
FROM 
    tblXC1Header
WHERE 
 NOT (tblXC1Header.XC1HeaderPlant="41c" 
     AND tblXC1Header.XC1HeaderFormulaCode="10898395");

---

Michał

Answer 2

Hi Michal, Yours works! Why does mine NOT work?

Brad

Answer 3

... Sorry I forgot to add. The SQL I posted was a test query. The real query I need to get working is this one. How do I apply your SQL format to my SQL?

SELECT tblXC1Header.XC1HeaderID, tblXC1Header.XC1Header, tblXC1Header.XC1HeaderPlant, tblTranslationTable.M3PlantCode, tblXC1Header.XC1HeaderFormulaCode, tblXC1Header.XC1HeaderDescription, tblXC1Header.XC1HeaderBatchSize, tblXC1Header.XC1HeaderSpeciesCode, tblXC1Header.XC1HeaderNumIngr, tblXC1Header.XC1HeaderStructureType, tblXC1Header.XC1HeaderRoutingTemplate, tblXC1Detail.XC1DetailID, tblXC1Detail.tblXC1HeaderID, tblXC1Detail.tblXC1Detail, tblXC1Detail.tblXC1DetailPlant, tblXC1Detail.tblXC1DetailFormulaCode, tblXC1Detail.tblXC1DetailIngredientCode, tblXC1Detail.tblXC1DetailIngredientPercent
FROM tblTranslationTable INNER JOIN (tblXC1Header INNER JOIN tblXC1Detail ON tblXC1Header.XC1HeaderID = tblXC1Detail.tblXC1HeaderID) ON tblTranslationTable.XC1PlantCode = tblXC1Header.XC1HeaderPlant
WHERE (((tblXC1Header.XC1HeaderPlant) Not In (SELECT [XC1HeaderPlant] FROM [qry41CEMEqual41CE_X_Formulas];)) AND ((tblXC1Header.XC1HeaderFormulaCode) Not In (SELECT [XC1HeaderFormulaCode] FROM [qry41CEMEqual41CE_X_Formulas];)));

I also forgot to say... THANKS for your reply!

Brad

Answer 4

I got brave and tried this:

SELECT tblXC1Header.XC1HeaderID, tblXC1Header.XC1Header, tblXC1Header.XC1HeaderPlant, tblTranslationTable.M3PlantCode, tblXC1Header.XC1HeaderFormulaCode, tblXC1Header.XC1HeaderDescription, tblXC1Header.XC1HeaderBatchSize, tblXC1Header.XC1HeaderSpeciesCode, tblXC1Header.XC1HeaderNumIngr, tblXC1Header.XC1HeaderStructureType, tblXC1Header.XC1HeaderRoutingTemplate, tblXC1Detail.XC1DetailID, tblXC1Detail.tblXC1HeaderID, tblXC1Detail.tblXC1Detail, tblXC1Detail.tblXC1DetailPlant, tblXC1Detail.tblXC1DetailFormulaCode, tblXC1Detail.tblXC1DetailIngredientCode, tblXC1Detail.tblXC1DetailIngredientPercent, [tblXC1Header].[XC1HeaderFormulaCode] In (SELECT [XC1HeaderFormulaCode] FROM [qry41CEMEqual41CE_X_Formulas];) And [tblXC1Header].[XC1HeaderPlant] In (SELECT [XC1HeaderPlant] FROM [qry41CEMEqual41CE_X_Formulas] GROUP BY [XC1HeaderPlant];) AS Test
FROM tblTranslationTable INNER JOIN (tblXC1Header INNER JOIN tblXC1Detail ON tblXC1Header.XC1HeaderID = tblXC1Detail.tblXC1HeaderID) ON tblTranslationTable.XC1PlantCode = tblXC1Header.XC1HeaderPlant
WHERE ((([tblXC1Header].[XC1HeaderFormulaCode] In (SELECT [XC1HeaderFormulaCode] FROM [qry41CEMEqual41CE_X_Formulas];) And [tblXC1Header].[XC1HeaderPlant] In (SELECT [XC1HeaderPlant] FROM [qry41CEMEqual41CE_X_Formulas] GROUP BY [XC1HeaderPlant];))=False));

Is this proper? Also it comes back with the results WAY faster than the previous one I posted (seconds vs. minutes). Any ideas why?

I also get the results I'm looking for.

Thanks,
Brad

Answer 5

Brad

They are 2 difrrent queries:

#1

WHERE
tblXC1Header.XC1HeaderPlant Not In (SELECT [XC1HeaderPlant] FROM [qry41CEMEqual41CE_X_Formulas])
AND

tblXC1Header.XC1HeaderFormulaCode Not In (SELECT [XC1HeaderFormulaCode] FROM [qry41CEMEqual41CE_X_Formulas])

#2:

WHERE
[tblXC1Header].[XC1HeaderFormulaCode] In (SELECT [XC1HeaderFormulaCode] FROM [qry41CEMEqual41CE_X_Formulas];)
And
[tblXC1Header].[XC1HeaderPlant] In (SELECT [XC1HeaderPlant] FROM [qry41CEMEqual41CE_X_Formulas] GROUP BY [XC1HeaderPlant];)=False));

In #1 you search XC1HeaderFormulaCode not in [qry41CEMEqual41CE_X_Formulas] in #2 you search in.

The second query is faster becouse you remove negation (not in). If you use not in is difficult to optimize by Rushmore...

And I don't know what is proper, only you have knowledge about yours data...

---

Michał

Answer 6

I want to filter out of my results where PlantCode = "41C" AND FormulaCode = "10898395" But I want to see the rest of the data.

Hi Brad,

You could try:

WHERE (PlantCode <> "41C" OR FormulaCode <> "10898395")

Imb.