Data indiscrepency

[Boon]

Hi

I have two tables that are related by CustomerID. Table A is the CustomerID
and its name. Table B is CustomerID and the transaction date.


Table A has a primary key on the ID. Table B doesn't have a primary key.

In the relationship, I force the data integrity for these 2 tables.

I have about 5 users using this database via back-end, front-end. It has
been used for about a month now.

Today, I log in to check the tables and found out that some ID in table B
are not in table A. How can this happen? How should I handle this?



Thanks.

 

[John Vinson]

I have two tables that are related by CustomerID. Table A is the CustomerID
and its name. Table B is CustomerID and the transaction date.


Table A has a primary key on the ID. Table B doesn't have a primary key.

In the relationship, I force the data integrity for these 2 tables.

Open the relationships window; rightclick the join line between these two
tables.

Does it in fact have the 'Enforce Referential Integrity' checkbox checked?

If it does, there may be some damage to an index. Try compacting and
repairing the database.

It might also be prudent to create an "Unmatched Values" query using the
wizard to see if there are more bad apples in the barrel!

 

[Allen Browne]

Suggestions:

1. Open the Relationships window (Database Tools tab of the ribbon in A2007,
or the Tools menu in previous versions.) Double-click the line joining the 2
tables in this window, to look at the properties of the relationship. Which
boxes are checked? Particularly:
Enforce Referential Integrty

2. Add a primary key to table B (just an AutoNumber if you like.)

3. Perform a compact/repair.
In Access 2007: Office Button | Manage | Compact/Repair.
In previous versions: Tools | Database Utilities | Compact/Repair.

4. Look under Tables, to see if there are any new tables there (such as
Compact Errors.)