Help With Relationship

[calvinle]

Hi,

I Wonder if the use of Relationship is absolutely needed to build an access database??

There is an alternate way to work with table without having them in Relationship.

I have read thru many document about Relationship, but I still cant figure out the way to work around what I needed, so I Wonder if Relationship is really needed?

Example:
A student can have many classes.
Each class can have many exam.
Each class can have many activity.
Each exam can have many corrector.
Each activity can have many instructor.

If there is no Relationship needed, i will create my table this way:
tblStudent:
[student_id] PK
[student_name]
[student_address]

tblClass:
[id] PK
[student_id]
[student_class]
[class_local]

tblExam:
[id] PK
[student_id]
[student_class]
[exam_name]
[exam_corrector]

tblActivity:
[id] PK
[student_id]
[student_class]
[activity_name]
[activity_instructor]

Example of data:
tblStudent:
A225 - Tom Cruise - 1144 Street
A226 - Selina Rom - 3343 Street

tblClass:
1 - A225 - Math - L305
2 - A225 - Physic - L303
3 - A225 - Chemistry - L303
4 - A226 - Math - L305
5 - A226 - Education - L304

tblExam:
1 - A225 - Math - Exam1 - John Corrector
2 - A225 - Math - Exam2 - Sylvia Corrector
3 - A225 - Math - Exam3 - John Correct
4 - A225 - Physic - Exam1 - John Correct
5 - A225 - Chemistry - Exam1 - Richard Corrector
5 - A225 - Chemistry - Exam2 - John Corrector
6 - A226 - Math - Exam1 - Richard Corrector
7 - A226 - Education - Exam1 - Richard Corrector

tblActivity:
1 - A225 - Math - Activity1- Eric Instructor
2 - A225 - Physic - Activity1 - Eric Instructor
3 - A226 - Math - Activity1 - Eric Instructor
4 - A226 - Education - Activity1 - Manon Instructor
5 - A226 - Education - Activity2 - Manon Instructor

For each student in the form, I will need a subform to display all their class unique only.
I will need to display their activity, and exam as well.

It's easier for me to work with VBA to have the data filter for the student, but I just don't know if that is the right way, because I cant figure out how to make the Relationship works in this scenario.

I am stuck at the tblClass which has relation to tblActivity and tblExam.

Can anyone explain if I can just stay out the Relationship or it is important to have the Relationship built in?

Thanks.

 

[plog]

You don't have to use the Relationship Tool in Access. But you will still have relationships among your tables. From what you have posted, yours are wrong.

All in all, you don't have enough tables and you don't have the right relationships among the ones you do have.

Where you head off course is tblClass. Which really isn't a table for your class information, it would be more accuracte to call it an enrollment table. A class table would have just information about the class (Name & Location), not the people in it.

I suggest you add an actual class table and rename tblClass to tblEnrollment. From there, you really need to think about the relationship between Activities and Exams.

How does John Corrector fit into an Exam? Will he always be assigned to every Class/Exam/Student as you have now or is he just assigned to every Class/Exam? Can individual students in a class have different people for the same exam? Same question for Activities.

 

[nstratton]

Take a look a the pictures I have attached. They part of a database I am building now and I think is a good example of what you are looking for.

Relationships are definitely useful and extremely vital to a good database. They allow you to keep your table sizes (relatively) small but still provide all the information you need.

In my database, I basically created lists for everything I could think I would need (States, Countries, etc) and then was able to link where necessary. The tables are storing the ID for said country or state but when the user runs a query they can see the full name or any other information you have stored.

My 2nd picture is an example of what I am talking about. Take States for example, instead of displaying the two letter abbreviation, the query returns the full name because of the link made.

I've added a couple links that I think you might find helpful as well.

http://allenbrowne.com/casu-06.html
https://support.microsoft.com/en-us/kb/304466

 

[calvinle]

Thanks for your help.

The main unique identifier here is student_id, that is why I carry that same value over table. In case I want to pull the activity of only a student, i will only need to filter the student id off the tblActivity.

John Corrector only fit to Class/Exam.
An exam can be corrected by multiple corrector.
An activity can be instructed by multiple instructor.

If I rename the tblClass to tblWnrolment, then what ahould it contain in tblClass?

Thanks

 

[plog]

If I rename the tblClass to tblWnrolment, then what ahould it contain in tblClass?

Based on the data you have provided in your first post enrollment and classes should look like this:

tblClass
class_ID, autonumber, primary key
class_Name, text, name of class ('Math', 'Physics', etc.)
class_Local, text, holds class_local data from initial post

tblEnrollment
enroll_ID, autonumber, primary key
ID_Student, text, foreign key to tblStudent.student_id
ID_Class, number, foreign key to tblClass.class_ID

Then your exam table would look like this:

tblExam:
exam_ID, autonumber, primary key
ID_enroll, number, foreign key to tblEnrollment.enroll_ID
exam_name, text, name of exam
ID_employee, number, foreign key to tblEmployees.emp_ID

 

[calvinle]

Based on the data you have provided in your first post enrollment and classes should look like this:

tblClass
class_ID, autonumber, primary key
class_Name, text, name of class ('Math', 'Physics', etc.)
class_Local, text, holds class_local data from initial post

tblEnrollment
enroll_ID, autonumber, primary key
ID_Student, text, foreign key to tblStudent.student_id
ID_Class, number, foreign key to tblClass.class_ID

Then your exam table would look like this:

tblExam:
exam_ID, autonumber, primary key
ID_enroll, number, foreign key to tblEnrollment.enroll_ID
exam_name, text, name of exam
ID_employee, number, foreign key to tblEmployees.emp_ID

I cant joint the Relationship from tblEnrol to tblExam??

View attachment sample.accdb

 

[plog]

Looks like you went primary key happy. If you have an autonumber field in a table, that and that alone should be your primary key.

Also, there's no reason to have 2 different id fields in employees.

 

[gemma-the-husky]

when you make a query you will link a field(s) in one table to a field(s) in the other.

this will be a one to many link. On the one side you probably (but not necessarily) use the PK. On the other you use fields in the table which constitute a matching FK (foreign key). Definitely not the PK on the many side.

If you declare this in a relationship, then access will automatically set this when you make a query.

 

[HiTechCoach]

I Wonder if the use of Relationship is absolutely needed to build an access database??

Absolutely. There is no reason not to.

There is an alternate way to work with table without having them in Relationship.

Sure. Write a lot of code to duplicate the Enforcement of Referential Integrity. But why reinvent the wheel.

Why not let the Database Engine take care of it for you. It is built in. This will eliminate the chance of bad data creeping in.

I have read thru many document about Relationship, but I still cant figure out the way to work around what I needed, so I Wonder if Relationship is really needed?

Just because you can't figure out model your data does not mean it is not needed.

Learning to model data is a skill that takes years to master.

The goods news is that you are getting close. I think you are missing a single table.

I also would make a single table for people (employees and Students). That way an employee can also be a student without any data duplication.

If you get the design correct for the database schema then creating forms and re0port will be very much simpler. Very little, is any, extra VBA code will be required.

I took a few minutes and modified your database to how I have done this in the past. I have database in use that you this design.

 

[calvinle]

Thank you very much! This really help me through my relationship development and thanks also for responding to my question. I am pretty sure once I master access for several years, it will be much easier for me.