Add field to flag duplicates in another field

[islu0102]

Good Afternoon!

I am looking for a statement that I can add to a query in design view that will create a new field that returns a "True" or "False" if the data in "Field C" is duplicate for all records in which "Field B" has a certain value. In Excel, I would simply use a COUNTIF formula, but I cannot find the equivalent in Access.

For example:

Item Status Location NewField
12345 Here Boston FALSE
12354 There Boston TRUE
12145 There Boston TRUE
15632 There LA FALSE
12145 Here LA TRUE
12354 Here LA TRUE

In the example, Location "Boston" is TRUE when Status = "There" because it is a duplicate in the Location field. The same for "LA" and "Here".

Any help would be appreciated!

Regards,

 

[Scooterbug]

A simple IIf statement will do the trick:

NewField: IIF("[Status]=Here","False","True")

 

[islu0102]

Thanks for responding so quickly, Scooterbug, but I'm looking for the formula to return a true / false for each record in which "Location" is a duplicate for a selected criteria for "Status".

For example, if I have 2 records for a Status of "There" and a Location of "Boston". If I choose the criteria to be "There" for Status, I would like to have the NewField return True for the records that have a Location of "Boston" (since they are duplicates). Otherwise, "" or False.

 

[Scooterbug]

Yea, my bad...I misread your first post

I did some playing around...and came up with this. I created a function in a new Module:

Function FindCount(sLocation As String, sStatus As String) As Integer

FindCount = DCount("*", "table1", "[location]=" & Chr(34) & sLocation & Chr(34) & " and [status]=" & Chr(34) & sStatus & Chr(34))
 
End Function

Then, setup a new field in a query like this:

TheCount: IIf(findcount([location],[status])>1,"True","False")

What that does is the Findcount sends the location and status to a function which uses that data to count the records that match. The IIF statement says that if the number returned is greater than 1, then the field will return a True. If it's not greater than 1, it will return a false.

 

[islu0102]

I guess another way to look at it is that I need a formula to fit into an iif statement like so:

NewField: IIf(([Status]="There" And {???(where ??? = a way to register that Location is a duplicate)},"TRUE","")

I'm not what you would call an experienced writer of statements.

Thanks!

 

[islu0102]

ooooooh.... shiny. I'll give it a try right now.

 

[islu0102]

Worked Perfectly! Thank you, thank you, thank you!

 

[Scooterbug]

Glad you figured it out