No more than two identical values

[RvVelzen]

Hi everyone,

Being an absolute noob, I hope anyone can help me with the following question:

I have a number field in Access 2003 for which I want identical values to exist not more than twice. A possibly complicating factor is that I do want to allow unlimited null values. Preferrably, I should also be able to run this criterion over data that has been entered already before.

How can I accomplish this best, through a validation rule, or VBA coding in the form combo box that I use for input, or somewhere else?

.. and what expression / code should I use?

Any help is greatly appreciated!

Robin

 

[DCrake]

Create a query that groups by the field in question and also performs a count on the same field. Then in the criteria row enter = 2 this will filter out all recrods that have 2 instances of the code in the table.

 

[RvVelzen]

DCrake, thanks so much for your reply and for helping me with my question.

Your solution seems a good one, unfortunately I haven't been able to implement it so far. As mentioned before I am a total noob so I am probably missing something obvious...

I have created a query with a column based on the field in question. I have set the column totals to 'Count' and the criterion to 2. But it is not working because this column always gives 1, even when some codes have many instances in the table.

If you could help me correcting my query, I would be very much obliged.

Many thanks in advance

 

[DCrake]

Have you clicked on the sigma button on the task bar looks like an M on its side.

Your query will now have the grouping row in the schema. Change the field in question from Group By to Count

 

[RvVelzen]

Hi DCrake,

Thanks so much for helping me. Now it works perfectly fine.

However, this query will only filter the codes that occur in the table an x number of times (2 in this case), and does not limit the input. Nevertheless it works very well to identify the >duplicated values so that I can change this in my existing table.

For limiting the input I have found the following solution in other threads: To the form combo box I have added the following Before Update event procedure:

Private Sub specimen_BeforeUpdate(Cancel As Integer)
If DCount("[specimen]", "tblImages", "[specimen]= " & Me.specimen & "") > 1 Then
   MsgBox "Duplicates Found"
   Cancel = True
End If
End Sub

Concluding, your solution combined with the VBA code should solve my problem nicely.

Thanks again for all the help!