Making relationships between multiple tables and enforcing integri

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK I have four table and this is how they are laid out:

Students Table
WID (PRIMARY KEY)
LAST NAME
FIRST NAME
CLASS

GRADES TABLE
WID (COMPSITE KEY)
CRN (COMPSITE KEY)
GRADE

COURSES
CRN (PRIMARY KEY)
TITLE
CREDIT HOURS
TEACHER E-MAIL

TEACHERS
LAST NAME
FIRST NAME
TEACHER E-MAIL (PRIMARY KEY)
RANK
DEPARTMENT

I can get a relationship between all but I am unable to enforce referential
integrity between all and the book I am using to learn this program says I
can do it. Unfortunately the book does not detail this process step by step.

Any help would be greatly appreciated.
 
OK I have four table and this is how they are laid out:

Students Table
WID (PRIMARY KEY)
LAST NAME
FIRST NAME
CLASS

GRADES TABLE
WID (COMPSITE KEY)
CRN (COMPSITE KEY)
GRADE

COURSES
CRN (PRIMARY KEY)
TITLE
CREDIT HOURS
TEACHER E-MAIL

TEACHERS
LAST NAME
FIRST NAME
TEACHER E-MAIL (PRIMARY KEY)
RANK
DEPARTMENT

I can get a relationship between all but I am unable to enforce referential
integrity between all and the book I am using to learn this program says I
can do it. Unfortunately the book does not detail this process step by step.

Any help would be greatly appreciated.
Click to expand...

How are you trying to establish the relationship? What specific problem are
you having?

You should be able to add STUDENTS, GRADES and COURSES to the relationships
window, and drag WID from STUDENTS to GRADES; if the fields are of the same
datatype (or if STUDENTS.WID is an Autonumber and GRADES.WID a Long Integer)
you should be able to choose the Enforce Referential Integrity. Similarly for
Courses and TEACHERS (joining TEACHER EMAIL to TEACHER EMAIL).

I'd suggest remofing the redundant word Table from your tablenames, and
avoiding blanks and special characters such as - in table and fieldnames, but
that's more a style issue than a requirement.


John W. Vinson [MVP]
 
John:

I am try to enforce integrity between each table and that is where I run
into issues. Access gives me a violation error message.

I can create all the relationships but it will not allow enforcing integrity
between all tables.
 
John:

I am try to enforce integrity between each table and that is where I run
into issues. Access gives me a violation error message.

I can create all the relationships but it will not allow enforcing integrity
between all tables.
Click to expand...

Then correct the errors.

We can't see the tables, we don't know what fields you're relating to what,
and we can't see your error messages.

Note that if you have the tables already populated, then you will be unable to
create referential integrity if there are records in the table which would
violate those constraints. You may need to edit or delete "orphan" or garbage
records prior to setting the relationship.

John W. Vinson [MVP]
 
Is there a way to email you my file to see what I did wrong and Explain what
I did wrong?

I followed the steps I have in the book to learn this program and it
appearently expect me to run into this issue.
 
Is there a way to email you my file to see what I did wrong and Explain what
I did wrong?
Click to expand...

At my standard consulting rates. Sorry, private service is for paying
customers.

If you could, just answer the questions. We should be able to solve the
problem here on the newsgroup.

John W. Vinson [MVP]
 
John W. Vinson said:
At my standard consulting rates. Sorry, private service is for paying
customers.

If you could, just answer the questions. We should be able to solve the
problem here on the newsgroup.

John W. Vinson [MVP]
Click to expand...
 
John:

I am try to enforce integrity between each table and that is where I run
into issues. Access gives me a violation error message.

I can create all the relationships but it will not allow enforcing integrity
between all tables.
Click to expand...

My apologies, Gazer. I slammed you for not answering questions; I'd neglected
to ask them.

What are the definitions of your Tables? Please post the tablenames, and the
names and datatypes of each field that you're using for join.

What specific error messages are you getting?

Are the tables currently empty, or do they already contain data?

If they do contain data, might there be existing records which violate the
rules that you're trying to establish?

Again... sorry about the sarcasm. It was inappropriate.

John W. Vinson [MVP]
 
No problem. I think I will get this issue resolved with someone else I know
that knows Access fairly well and will not charge me if he or she has to dig
into the database to find out what is wrong. Since I am new enough to Access
I think I am just know explaining well to you the trouble I am having.
 
Back
Top