CODEWITHSUNDEEP
ms-accessduplicates
gtimmies
gtimmies

Reputation: 1

Mark Duplicate in Access

I would like to write a query in Access 2013 to mark the first time a word appears as "Yes" and all subsequent times as "No". I have included a sample below. The "Distinct" column is what I would like my query to generate.

Thank you

Word      Distinct
First     Yes
Second    Yes
First     No
Third     Yes

Upvotes: 0

Views: 338

Answers (1)

June7
June7

Reputation: 21389

Here is one approach:

SELECT *, IIf(DMin("ID","Table1","[Word]='" & [Word] & "'")=[ID],"Yes","No") AS FirstWord
FROM Table1;

Another:

SELECT *, IIf([ID] = (SELECT TOP 1 ID FROM Table1 AS Dupe WHERE Dupe.Word = Table1.Word ORDER BY Dupe.Word, Dupe.ID), "Yes", "No") AS FirstWord FROM Table1;

DISTINCT is a reserved word so I am avoiding using it as a field name.

Upvotes: 1

Related Questions