Create a "Flag" column based on duplicate column values and extra criteria

Question

This is my raw table:

Name    Age Group
Paul    20  1
Paul    20  2
Paul    30  3
Paul    30  3
Paul        2
Paul        2
Joe     15  1
Mary    20  3

How do I write MS Access SQL to exclude records bases on criteria below?

Ideally, I want to create an additional "Flag" column to populate value "True" for records to exclude, as I may have further excluding criteria later.

1. Within the records with identical "Name":

   (1) Their "Group" are 1 or 2

   (2) Their "Age" are identical but not blank

2. For records with unique "Name", leave as is

In this case, only first and second record will be excluded.

Updates:

@xQbert, thank you very much!

To make it easier, I want to first set flags for duplicate "Paul" columns, but the code returns an error:

UPDATE A
SET A.Flag = "True"
FROM
(
SELECT Name
FROM tblTest
GROUP BY Name
HAVING COUNT(*) > 1
    ) T INNER JOIN tblTest A ON T.Name = A.Name

But this works fine.

SELECT A.Name, A.Age, A.Group
FROM
    (
    SELECT Name
    FROM tblTest
    GROUP BY Name
    HAVING COUNT(*) > 1
    ) T INNER JOIN tblTest A ON T.Name = A.Name

Answer 1

Something like this should give you the list of ones you want to flag, from this you could create the update...

SELECT [A].[name], [A].[Age], iif([A].[Group] =1,2,[A].[Group]) gp, count(*)
FROM Raw_table A
WHERE Age is not null and age <> ""
Group by [A].[name], [A].[Age], iif([A].[Group] =1,2,[A].[Group])
having count(*) > 1 

--Update to to show... update statement... hate those double words. --There's a better way to do this but w/o Access... it's hard for me to figure out the right syntax.

--USING & to contat I can't remember if Access uses + or & --whereclause needed or you get your blank ages set to true...

UPDATE tblTest set SET A.Flag = "True"
WHERE Name&Age in (SELECT Name&Age
FROM tblTest
WHERE age is not null and age <> ""
GROUP BY Name&Age
HAVING COUNT(*) > 1)

Answer 2

Ok. I am doing this in Access 2010.

This query returns the keys you want to exclude:

SELECT Name, Age, Count(*) AS Occurs
FROM Data
WHERE Group In (1,2) AND Age Is Not Null
GROUP BY Name, Age
HAVING Count(*)>1;

I saved this as "QueryExcludeKeys".

To update the flag I am trying these queries:

UPDATE Data d, QueryExcludeKeys e
SET d.Flag=True
WHERE d.Name=e.Name AND d.Age=e.Age AND d.Group IN (1,2)

UPDATE Data d
SET d.Flag=(
   d.Group IN (1,2) AND
   EXISTS (
      SELECT * FROM QueryExcludeKeys e
      WHERE e.Name=d.Name AND e.Age=d.Age
   )

... neither of which will work. It states that it requires an updatable query, which is a bit frustrating since I am only requiring updates to one column of the Data table. It is true that QueryExcludeKeys is not updatable (it contains a GROUP BY), but this should have nothing to do with updating Data.

I'm looking for other solutions.

As an aside... "Name" and "Group" are awful names for real table columns. You should avoid using reserved words in column names and many other places. :)