relationships

[Mutya]

This is probably a stupid question but pls indulge me. Can you still define
relationships after the tables have been populated? I think I wasn't paying
close attention when I designed my database. Now I realise that I need to
build new relationships and delete others. Could I still do this when the
fields are no longer empty?

Thanks.

Mutya

 

[Fred]

Yes you can.

Of course, certain things that you already put in there / did may cause
issues with creation of joins. For example, certain joins require the there
be no duplicated values in the field that is a part of the join, and if
you've already entered duplicated values in that field, that will cause an
issue.

 

[John W. Vinson/MVP]

This is probably a stupid question but pls indulge me. Can you still define
relationships after the tables have been populated? I think I wasn't paying
close attention when I designed my database. Now I realise that I need to
build new relationships and delete others. Could I still do this when the
fields are no longer empty?

You may well need to edit some of the records in the table, if there
are records which would violate the relationship.

Let's say you have a classic school enrollment application with a
table of Classes, a table of Students, and a table of Enrollment; the
latter would be related one-to-many to Classes by ClassID, and to
Students by StudentID.

If you created and filled the tables with no relationships enforced,
you might well have a record in Enrollment with ClassID 1 and
StudentID 1254 - despite the fact that there is no student with
StudentID 1254, and no record in the Students table with that ID.
Should that be the case, Access won't let you create the relationship!

The solution is to find this bad record, and either delete it, or
correct the StudentID to 125 (the actual ID of the student enrolled in
Class 1). Naturally this process may be a bit difficult if you don't
know what the data should have been, reinforcing the benefits of
maintaining integrity in the first place!