Hi. You don't tell us what the data type of the Team Member primary key is. If it is an autonumber then the matching data type in your reviews tables should be a
long integer.
To find non-matching rows which would break relational integrity create a new query in which you join the review table to the team member table. Select the join and change it to a left or right join as appropriate - the one that says include all rows from the review table and only the one from the team member table that match. Include suitable fields from your review table (and particularly the team member ID secondary key). Include the team member primary key from the Team Member table, and in its criteria box enter
Is Null. When you run the query you will see only the rows where there is no match for the team member ID in your review table to your team members table.
As a side comment, I am certain that your tables are not in completely normalised form. If you have two tables that are identical except that one is for a 6 month review and the other is a 12 month review you have mistakenly confused an attribute - the review period - with the need for another table. You should just have one review table, which includes as an attribute a definable review period that in turn is specified in a separate review periods table.
What happens if you decide you need another review at 9 months? You should never need to add another table...
We have a useful introductory article on
data normalisation and table structures which you may find of interest.
The main thing to remember is that in a normalised table all attributes - the fields of that table - must be dependent solely on the primary key and nothing but the key. Any other kind of dependency - such as the implicit one about review periods, which is hidden away in your current design by using multiple tables - requires further decomposition before the design can be considered normalised.
-Stewart
38
