A referential integrity issue involves establishing a relationship where a value (for example an ID value) in one table must correspond to a value (ID value) in another table. Breaking the rules of referential integrity can cause "orphaned records/values". Consider these 2 tables you have...
and these values in the tables...
1,1 West Wash Ave,Programmer,12/27/1964,22
2,22 Park St,Sales Agent,03/21/1955,29
3,21 W. Park St,Some Profession,02/01/1945,24
24,Bill's Company,Sales Manager,221-3433,01/01/2004
In the above scenario, you could NOT establish referential integrity between the Customer and Agent table linking the agent_ID field because this record...
2,22 Park St,Sales Agent,3/21/1955,29 in the Customer table
breaks the rules of integrity (ie. there is no agent_ID = 29 in the Agent table.) Thus, the 29 becomes an unmatched value and no referential integrity can be established between the agent_ID fields between these 2 tables (a corresponding agent_ID number must exist in the Agent table to be used for agent_ID in the Customer table.) In order to establish referential integrity between these 2 tables and the agent_ID field, 29 would need to be removed from the agent_ID field in the Customer table for that record. You should always try to establish referential integrity in this kind of scenario to ensure you would not get "ID" values in 1 table which do not correspond to any "ID" values in another table (which is often the culprit of inaccurate reports!)
As a note, when I establish relationships between 2 ID fields, I usually select the "Cascade Delete" (depending if I wanted related records in the other tables deleted or not) and "Cascade Update" options. Cascade Update means that if (in the above example in the Agent table), agent_ID 22 where changed to 32, all subsequent 22 agent_ID values in the Customer table would change to 32. Cascade Delete means that if I tried to delete agent_ID 22 in the Agent table, depending on how I set up the relationship, all "Customer" records with agent_ID 22 would be deleted (and I would get an Access message that deleting agent_ID 22 would cause cascade deleting of related records in the Customer table), or I would get an error stating that I was violating the rules of integrity because agent_ID 22 existed in the Customer table.
Use this same concept as a guide for your other tables and the ID fields when creating relationships.
As a last note, establishing referential integrity should be done BEFORE you start entering data. If you already have data in the tables, when you try to establish referential integrity (i.e. relationships), Access (or SQL Server) is going to check to make sure you don't break the rules of referential integrity with the existing data. If it finds an unmatched value between the 2 tables on the field you're trying to establish a relationship on, you'll get an error that it would violate the rules of referential integrity and you won't be able to establish the relationship until you FIRST, correct the data. To help you in finding unmatched values on existing data, Access has very nicely created a query in the wizard called: "Find unmatched values" which walks you through steps on being able to find unmatched values between fields on 2 tables.
The Northwind database which ships with MSAccess has some relational table structures you may want to take a look at. I haven't covered the one-to-many, many-to-many, and many-to-one relationship types here but if you look at the Northwind database, I believe you can see different examples of these.
Hope that helps.
Last edited by pkstormy; 10-04-07 at 13:52.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)