Records in relational tables

[Shawn]

I have an existing table. I created a second table and a one to many
relationship. When I try to enter a record with the relational information I
get an error. It states that the record must exist in the primary table.
The problem is that it already does. When I go to the primary table and
creat a duplicate of the existing record then it alows me to enter a record
in the secondary table. Why is this happening and what do I do to correct it?

 

[Ken Snell [MVP]]

Tell us the details of the two tables. And the process that you're using (a
form? with a subform? directly into the tables?) to enter the data. Sounds
as if your first record in the table didn't get stored yet when you try to
enter the data into the child table.

 

[Shawn]

The parent table is my Drawing table. The drawing number is the primary
key.
I have several child tables. For example my Instrument Loop table uses a
loop number as a primary key. I form a relationship using the Drawing number
and the loop sheet number. When I created the Drawing table I used an input
mask and designated a field size of 18 for the Drawing number field. When I
created the Instrument loop table I used the same input mask but realized
that I only needed 16 characters for the field size. So I made the field
size 16 in the loop table and then changed the field size in the drawing
table.
Another interesting note: The parent table will allow me to copy a
record and create another duplicate record. Then I delete the first record
and my child table will then allow me to enter the loop number which is the
same as the corrisponding drawing number. I should not be allowed to create
a duplicate record in the parent table because there are two identical
primary keys (the drawing number) I don't know why this is allowed.--
Shawn

 

[Ken Snell [MVP]]

When discussing table fields, relationships, keys, etc., it's critical that
the details are exact. When I read your note, I see a reference to a "loop
number" as a primary key, then you discuss a relationship using the "Drawing
number" and the "loop sheet number". Is "loop number" the same thing as
"loop sheet number"? If "loop number" is the primary key, why do you have a
relationship between two fields from the parent table to the child table --
normally, you would use just the parent's primary key for the relationship.

Did you establish the relationship while the parent's primary key was 18
character size in the field's "size" property? If yes, delete the
relationship, remove the primary key setting from the field, save the table,
set the field to the primary key again, and re-establish the relationship;
ACCESS may not have updated the relationship with the new property size. The
duplicate record entry also likely arises because the primary key initially
was established with an 18-character width, but the new "duplicate" record
has just a 16-character string; again ACCESS likely needs to reset the index
with the correct properties.


--

Ken Snell
<MS ACCESS MVP>

 

[Shawn]

The parent table is called Drawing Table and is a list of all the drawings I
am responsible for keeping current. This table has a primary key called
Drawing Number. Each drawing has an assigned number which follows a
predetermined format. There are several types of drawings One of them is
called a loop sheet. Loop Sheets depict the different points used to conect
one or more field devices to our distributive control System. We call this a
control loop. Each control loop is assigned a four digit number called a
loop number or just loop. I have created a table to keep track of the
assigned loop numbers and their corresponding loop sheets. So the loop
number (a four digit number) is the primary key in this table. The loop
sheet drawing number is used to relate the two tables because the loop sheet
drawing number is the same number in both tables.
Next I am creating a table for each device or piece of equipment in the
field. I am working on creation relationships between these tables and the
two previously mentioned table also. I hope this helps. Let me know if you
need more information.

 

[Ken Snell [MVP]]

I apologize, but without seeing a list of the table names and the fields
within each table, and some sample data, to show how the tables should be
related, I am not following your description -- your familiarity with your
data makes it clear to you, but my "outsider" point of view is not letting
me see it clearly.

Try listing the info this way:

TableName1
FieldName1 (primary key)
FieldName2
FieldName3

TableName2
FieldName1 (primary key)
FieldName2 (foreign key from tablename)
FieldName3

etc.


--

Ken Snell
<MS ACCESS MVP>