Updating Tables

[Guest]

Sorry if this is a repeat, but I could not find the answer in the archived
posts.

I have two tables that have a one-to-many relationship with one another.
The linked field is "ID," a primary key and autonumber for "tblExam," and a
primary key and long integer in "tblExamData." "tblExamData" was created to
hold several years of exam data (% of students answering correctly, etc...)
for each exam question in "tblExam."

With this relationship as it stands now, I cannot enforce referential
integrity. When I try, I get an error message saying that "tblExam" violates
referential integrity rules. Secondly, "tblExam," which contains exam
questions and other data, is constantly changing and growing. When a
question is added or deleted from "tblExam," how do I make sure this change
is updated on "tblExamData?"

Many thanks in advance for your help. More information is available upon
request.

Best,

Joe

 

[Donnie Keith via AccessMonster.com]

If you have a field on all the tables that are identical for instance if
you had SSN's on each field.....

One table, the main table would have the autonumber and an SSN, the
autonumber would be the primary key and the SSN would need to be indexed
with no duplicates. That's the main table

Now you move on to the next table which is related to the main table based
on the SSN. Create another autonumber as the primary key and input another
SSN field, except this one you index with duplicated OK. what this allows
is that many fields may be entered ie grades for different days may be
entered for a student.

After you have done this, make sure there are no records inputted in either
table yet, then do a tools, compact and repair database. After that go to
tables relationship and create a new relationship with the two tables, this
will allow referential integrity.

Now to pull it all together create a query for the main table and the other
table. Then create a form for the main table and a subform for the other
table. Insert the subform into the main table you will be asked how the
fields are linked, I always choose define my own and click the drop down
boxes for the links. This will now hold your data for each individual.