Restoring referential integrity

[John Melbourne]

Dear Readers

I have an access database that I set up using tables in which referential
integrity was enforced in all relationships between tables.

I have been playing around with the relationships in this application and
now when I try to restore the original relationship with RE enforced it wont
allow it - due to a violation of RE.

What I am trying to do is find the bug record in a very large table of data
so that I can restore the relationship with RE enforced.

Some way of counting the number of unique entries in a key field.

 

[Dirk Goldgar]

Dear Readers

I have an access database that I set up using tables in which
referential integrity was enforced in all relationships between
tables.

I have been playing around with the relationships in this application
and now when I try to restore the original relationship with RE
enforced it wont allow it - due to a violation of RE.

What I am trying to do is find the bug record in a very large table
of data so that I can restore the relationship with RE enforced.

Some way of counting the number of unique entries in a key field.

You can use SQL like this in a query:

SELECT [KeyField], Count([KeyField]) As Cnt
FROM MyTable
GROUP BY [KeyField];

If you want to find only KeyField values that are duplicated, you can
add a HAVING clause:

SELECT [KeyField], Count([KeyField]) As Cnt
FROM MyTable
GROUP BY [KeyField]
HAVING Count([KeyField]) > 1;