Validate datatable (MS Access)

[ErikJan]

When one wants to couple two tables, one can create a relationship (Relationship tools - Edit Relationships). There is an option "Enforce Relational Integrity" which should normally be enabled to ensure that the relationships are all valid.

If in one of the tables there is an error which prevents this option, it is greyed out. My question is: HOW can we find the place where the error(s) are that cause this???

Imagine a 20K record table with one little error... there must be a way I guess... Anyone who can help me?

[HansV]

Make sure that the fields on either side of the relationship are of the same type and size. For example, both fields are number fields, and both are Long Integer. You cannot enforce referential integrity if one field is Long Integer and the other Double.

If there are data in the tables that make enforcing referential integrity impossible, the check box will still be enabled, but if you tick it and click OK, you'll get an error message that Enforce Referential Integrity couldn't be applied.

[ErikJan]

Is is disabled here but even then... if you get the warning you mention that integrity cannot be enabled, the Access KNOWS this. But if I want to fix the problem, I'd like to know WHERE the problems are. Again, as also Access knows, I would think that there should be a way to find that out

[HansV]

The first test would be to check the data types and field sizes of the join fields.

Next, you can use the Find Unmatched Query Wizard to find out if there are records on the "many" side of the relationship that do not have a match on the "one" side.
Click Query Wizard on the Create tab of the ribbon to start the Find Unmatched Query Wizard. The instructions should be self-explaining.

[ErikJan]

Thanks, that's what I was looking for