Search code examples
ms-accesssubqueryms-access-query

In Access query how to check if a group of records has the same value


In the following query for each group of "first", I want to check if the Values on field "Second" are true.
For example, in the "first" field for "A" Group if the related values on the "second" field are true, the "Third" field must display "True" for both records, and if the values are false or are different the "third field value must display "False"
I reached this approach by creating new query grouping the "First" values of the main query then I Called the related values on the main query by Dlookup where its group values are equal, but it is very slow.

First Second Third
A True True
A True True
B False False
B Fasle False
C True False
C Fasle False

Solution

  • Use Exists in a simple query:

    SELECT 
        Check.[First], 
        Check.Second, 
        Not Exists 
            (Select T.[First] From Check As T 
            Where T.[First] = Check.[First] And T.Second=False) AS Third
    FROM 
        [Check];
    

    Output (in Danish: Ja = True, Nej = False):

    enter image description here