Help in setting relationships in Access

[forest8]

Hi

I have created a database of Students.

The first table I have created has the Students Names and IDs.

Each table needs to have the students ID but in each table, I can not create
Enforce Referential Integrity.

The Student Table is the main source of details for this database. Any time
a student is added this information should be accessible to all the other
tables. I've created a lookup for all the tables that it should reference
the Student Table for ID numbers.

Any help is appreaiated.

 

[Arvin Meyer [MVP]]

Add the StudentID to each of the other tables as a Foreign Key. Then you can
create the relationship. Display the other tables as subform of the Student
table's form, or use a combobox to add the StudentID to other forms (and
their underlying tables) In order to build a relationship, the field MUST be
filled in or the child record cannot be saved.

 

[John W. Vinson]

Hi

I have created a database of Students.

The first table I have created has the Students Names and IDs.

Each table needs to have the students ID but in each table, I can not create
Enforce Referential Integrity.

Well... no. The table of Courses would certainly not have a StudentID, because
a Course will have *many* students; a table of Instructors will not have a
StudentID because... well, you get it. To model a many to many relationship
(each Student takes many Classes, each Class has many Students) you need a
third table - Enrollment let's say - with fields for the StudentID (who's
enrolled) and ClassID (what is she enrolled in).

The Student Table is the main source of details for this database.

Details ABOUT STUDENTS. Not every table needs to be related to the students
table.

Any time
a student is added this information should be accessible to all the other
tables. I've created a lookup for all the tables that it should reference
the Student Table for ID numbers.

Do not use Tables to interact with the data - use Forms instead. Lookup Fields
are *never* necessary, and can cause many problems; see
http://www.mvps.org/access/lookupfields.htm for a critique.

Instead use Forms with combo boxes, listboxes, subforms, and other such tools
to display and interact with your data.

You might want to look at Crystal's video or the tutorials here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[KARL DEWEY]

Your Student table needs to have a primary key field named something like
StudentID. Many folks use an Autonumber field as it automaticaly increments
the number to be unique as required for a primary key.
If using the Autonumber field then and related table needs a foreign key
field to match and that would be a number field - long integer.
DO NOT use a lookup for all the tables to
reference the Student Table for ID numbers.

In the relationship window place both tables to be related and click on the
primary key field of the Student table and drag to the foreign key field of
the related table. Select the Referential Integerity and Cascade Update
options.

If it will not allow the relations then run an unmatch query to find records
that do not have a coorelation. Fix the records and repeat the above.

Use a form/subform for Student/related entries with the Master/Child links
using the primary key/foreign key. As records are added to the subform
(student related table) the foreign key field will automatically be populated.