Not having created the keys or relationships shouldn't have caused the
problem with officers, so doing so might not solve it unless you've
inadvertently created an incorrect primary key or unique index in Capacities.
Nevertheless it should be done. You should not have to run the queries
again though; the data which has been inserted into the tables should not
conflict with the keys or relationships. Incidentally a foreign key is not
set in the table design, it’s a by-product of creating a relationship.
Taking it table by table:

1. Companies

Set the Company name field as the primary key by selecting it in table
design by clicking on the field selector (the little square on the far left
of the field), right clicking and then selecting primary key from the
shortcut menu.

If there are any other fields, e.g. Incorporation #, in this table whose
values will be unique in the table, i.e. the same value can't appear in more
than one row the index the field uniquely by selecting Yes (No Duplicates) as
its Index property in its properties sheet.

2. Personnel

Set the PersonelID field as the primary key in the same way as above.

3. Capacities

The primary key of this table is all the fields in combination, so select
all three by clicking on the field selector of each in turn while holding
down the Ctrl key, or by clicking a dragging down over all three field
selectors. The right click and select primary key. You should then see the
key symbol against all the fields.

The PersonnelID and Company name fields in this table will both be used as
foreign keys, so index each of them non-uniquely by selecting Yes (Duplicates
OK) as the Index property of each. The indexes will improve performance
when the tables are joined in queries or linked in a form/subform.

Relationships:

To create the relationships open the relationships window. With the mouse
pointer inside the window right click and select Show table. Add the three
tables to the window and move them so that they are in a line with Companies
on the left, Personnel on the right and Capacities in the middle.

With the mouse click and drag from Company Name in the Companies table to
Companies Name in the Capacities table. The 'Edit Relationship' dialogue
will open; it should say 'one-to-many' at the bottom. In this check the
'Enforce referential integrity' check box and then click the Create button.
It should create the relationship, but if you get an error message then there
is something in the data which is preventing referential integrity being
enforced. This should not happen, but if it does uncheck the 'Enforce
referential integrity' check box and then create the relationship until
you've sorted out what the problem with the data is (it means that there is a
row in Capacities without a match in Companies).

Then do exactly the same between Personnel and Capacities by clicking and
dragging from PesonnelID in Personnel to PesonnelID in Capacities.

You'll see now how the Capacities table has resolved the many-to-many
relationship which exists between Companies and Personnel into two
one-to-many relationships. This is always how a many-to-many relationship is
represented, never directly between two tables. Tables like Capacities which
do this are sometimes referred to as 'junction' tables or some other similar
term, but that's just a folksy way of saying that they are modelling a
relationship between other tables, usually a many-to-many relationship, but
in some special circumstances it can be a one-to-many relationship.

With the keys and relationships set up as above your 'logical model' or
'schema' is now securely set up so that you can't enter invalid or
inconsistent data. However, I'm doubtful that it will solve the problem of
appending the officers to Capacities. As I said before one explanation of
that would be the presence of incorrect unique indexes, not the lack of
indexing.

Let me know how you get on with creating the keys, indexes and
relationships, and we'll take it from there.

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Oh no...I didn't set the Primary key and the 2 foreign keys for the
capacities table...actually, I didn't realize I had to do that. So I guess I
should redo all of my queries and such and try again. Do you have time to
let me know where my primary keys and foreign keys should be and list the
relationships to make sure that I have them right? At this point, I am
doubting that I even had them right...boy I feel like a real newbie. Thank
you so much for your patience and feedback with assistance. I have been
following your instructions and they have been working but I don't want to
have to redo again if I can avoid it. I do have a hard time with
relationships and now obviously designating the proper Primary and Foreign
Keys are an issue with me.