none
Filter query RRS feed

  • 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


    • Edited by mbrad Friday, November 27, 2015 2:40 PM added url
    Friday, November 27, 2015 2:19 PM

Answers

  • 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ł


    Friday, November 27, 2015 3:26 PM

All replies

  • 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ł

    Friday, November 27, 2015 2:48 PM
  • Hi Michal, Yours works! Why does mine NOT work?

    Brad

    Friday, November 27, 2015 2:51 PM
  • ... 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

    Friday, November 27, 2015 2:56 PM
  • 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

    Friday, November 27, 2015 3:06 PM
  • 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ł


    Friday, November 27, 2015 3:26 PM
  • 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.

    Friday, November 27, 2015 4:40 PM