locked
access sql RRS feed

  • Question

  • I got a table with the following fields (State, County, Municipality, Dataset, Processed). All are text except Processed which is Yes/No. The entries may look like this:

    1. FL   Brevard   Null      Traffic    True
    2. FL   Brevard   Null      Arrest    True
    3. FL   Brevard   Null      Misdem  False
    4. FL   Duval      Null      Traffic    True
    5. OH  Cuya      Obetz    Traffic    False
    6. OH  Cuya      Obetz    Felony    False
    7. TX   Travis    Louis     Misdem  True

    I need an SQL that returns State, County, Municipality where Processed is false for all datasets of any Municipality. In the sample data above, only OH, Cuya, Obetz should be selected. I got it to work with 3 queries but I'm sure it can be achieved with just 1.

    Thank you

    Tuesday, April 17, 2018 6:11 PM

Answers

  • Hi,

    Had a little time to do a little testing and below is a modified version of the above:

    SELECT T2.State, T2.County, T2.Municipality
    FROM Table1 AS T2
    WHERE (((Not Exists (SELECT T1.State,T1.County,T1.Municipality
    FROM Table1 T1 
    WHERE T1.Processed=True 
    AND T1.State=T2.[State]
    AND T1.County=T2.[County]
    AND NZ(T1.Municipality)=NZ(T2.[Municipality])))=True));
    Hope it helps...

    • Marked as answer by JnWayn Wednesday, April 18, 2018 7:34 AM
    Tuesday, April 17, 2018 7:50 PM

All replies

  • You could do it with a query with a subquery, or using (for example) DCount:

    SELECT State, County, Municipality
    FROM [MyTable]
    WHERE DCount("*", "MyTable", "State='" & [State] & "' AND County='" & [County] & "' AND Processed=True")=0

    where MyTable is the name of your table.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 17, 2018 7:14 PM
  • Hi,

    Untested but maybe you could try something like:

    SELECT State, County, Municipality
    FROM TableName
    WHERE NOT EXISTS (SELECT State, County, Municipality
    FROM TableName
    WHERE Processed = True)

    Hope it helps...

    Tuesday, April 17, 2018 7:18 PM
  • Hi,

    Had a little time to do a little testing and below is a modified version of the above:

    SELECT T2.State, T2.County, T2.Municipality
    FROM Table1 AS T2
    WHERE (((Not Exists (SELECT T1.State,T1.County,T1.Municipality
    FROM Table1 T1 
    WHERE T1.Processed=True 
    AND T1.State=T2.[State]
    AND T1.County=T2.[County]
    AND NZ(T1.Municipality)=NZ(T2.[Municipality])))=True));
    Hope it helps...

    • Marked as answer by JnWayn Wednesday, April 18, 2018 7:34 AM
    Tuesday, April 17, 2018 7:50 PM
  • I was aware of using dcount but I find it kinda messy. I was looking for the use of NOT EXIST or IN with maybe DISTINCT or GROUP BY
    Tuesday, April 17, 2018 9:44 PM
  • Bingo. The first experiment didn't work (I had to try it) but the modification (this) does. As an intermediary I tried using a join but got nowhere with that either:

    SELECT T1.[State], T1.[County], T1.[Municipality]
    FROM [Processor Datasets] AS T1
    INNER JOIN (SELECT [State], [County], [Municipality]
    FROM [Processor Datasets] 
    WHERE NOT EXISTS(SELECT [State], [County], [Municipality]
    FROM [Processor Datasets]
    WHERE [Processed])) AS T2
    ON T2.[State] = T1.[State] And T2.[County] = T1.[County] And Nz(T2.[Municipality]) = Nz(T1.[Municipality])

    It looks logically and sound but simply returns an empty set where I know it shouldn't. I only mention this for deeper understanding but thank you for the brilliant solution

    Tuesday, April 17, 2018 10:31 PM
  • Also a LEFT JOIN where T2 is null fails
    Tuesday, April 17, 2018 10:35 PM
  • Hi,

    Glad to hear you got it to work. Good luck with your project.

    Wednesday, April 18, 2018 1:21 AM
  • Hi JnWayn,

    You had mentioned that,"I need an SQL that returns State, County, Municipality where Processed is false for all datasets of any Municipality. In the sample data above, only OH, Cuya, Obetz should be selected."

    I try to create dummy table like yours.

    Query:

    SELECT State,County,Municipality from [Processor Datasets] where Municipality <> Null and Processed=false ;

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 5:21 AM