Solved Teachers database RELATIONSHIPS (1 Viewer)

ElizabethTaylor

New member
Local time
Today, 18:43
Joined
Mar 15, 2022
Messages
28
Hi. I am very new to Ms access. I am trying to make a teachers database. Have a look at the attached database. I am defeated on to make relationships.
Teacher A teaches a Subject like BIOLOGY in CLASS FORM 1 STREAM EAST IN A YEAR LET'S SAY 2022 TERM 1.THE DA
The same Teacher A can teach BIOLOGY CLASS FORM 1 NORTH.
How will I ceate relationship ? Please help me.
 

Attachments

  • Teachers.zip
    47.7 KB · Views: 206

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:43
Joined
Jul 9, 2003
Messages
16,244
You might find my blog here useful:-


It describes the relationship between student and hobbies, but could just as well be student and classes. If you substitute student for teachers, then you've probably got what you want...
 

GinaWhipp

AWF VIP
Local time
Today, 11:43
Joined
Jun 21, 2011
Messages
5,901
Hmm, you are missing some Junction tables. Perhaps this will help...
 

plog

Banishment Pending
Local time
Today, 10:43
Joined
May 11, 2011
Messages
11,611
Agree with the above also:

1. Only use alphanumeric characters and underscores in names. That means no spaces either, instead of FIRST NAME use FirstName, etc.

2. Store data as discretely as you can. You have a field for teachers last name and then a field for first and last name. Instead store the first name and the last name. There is no need to store them together.

3. Tables with only 1 real field of data are unncessary (autonumbers are not real data). Take tblYears for instance--instead of that table, you simply put the actual year into whatever table you want the year instead of the primary key for that field. Same works for tables with one real field that is text--just store the text in the external table instead of the primary key.
 

GPGeorge

Grover Park George
Local time
Today, 08:43
Joined
Nov 25, 2004
Messages
1,776
Hi. I am very new to Ms access. I am trying to make a teachers database. Have a look at the attached database. I am defeated on to make relationships.
Teacher A teaches a Subject like BIOLOGY in CLASS FORM 1 STREAM EAST IN A YEAR LET'S SAY 2022 TERM 1.THE DA
The same Teacher A can teach BIOLOGY CLASS FORM 1 NORTH.
How will I ceate relationship ? Please help me.
Here's a link to a demo of the many-to-many relationship being described. Its content is songs and artists who recorded them, but the parallel conceptually is the same. One artist recorded many songs. One song was recorded by many artists.
 

Mike Krailo

Well-known member
Local time
Today, 11:43
Joined
Mar 28, 2020
Messages
1,030
There doesn't seem to be any attendance records in there either so you might want to work that out as well.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 28, 2001
Messages
26,999
Designing tables for a new database is an interactive process. You look at what you think you want. Then design a table, only to find that you now need something else to relate to some other data element (e.g. students). So you add more tables only to find that what you built isn't quite compatible with what you really wanted. Back to the drawing board... with the nagging question "What did the guy who invented drawing boards go back to when that didn't work?"

The best method is to decide as much as possible ahead of time what you think you will need to DO and then design data to support that goal. To a large degree, we really can't help you until you get your goals lined up.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:43
Joined
May 7, 2009
Messages
19,169
another demo, i made "new" tables to hold some informations.
 

Attachments

  • Teachers.zip
    578.9 KB · Views: 241

ElizabethTaylor

New member
Local time
Today, 18:43
Joined
Mar 15, 2022
Messages
28
@arnelgp I am trying to understand the kind of relationships you used. I have realized it is not referential integrity. Kindly which relationships did you use.
 

mike60smart

Registered User.
Local time
Today, 15:43
Joined
Aug 6, 2017
Messages
1,899
@arnelgp I am trying to understand the kind of relationships you used. I have realized it is not referential integrity. Kindly which relationships did you use.
HI

The Relationships should be set as follows.
 

Attachments

  • ri.JPG
    ri.JPG
    75.8 KB · Views: 180

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:43
Joined
May 7, 2009
Messages
19,169
you do not need to Enforce Referential integrety to the other table.
setting it to the 3 table is enough.
2022-03-18_10-28-02.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:43
Joined
May 7, 2009
Messages
19,169
I think it is good to enforce referential integrity to prevent orphan records.
yes it is, it is Enforced on the 3 main tables involved.
and not on Teacher, year, terms, stream, grades, students.

imagine you delete a year from year table, then it will delete All
records for that year on the 3 main table?

also you update the year in year table, and all years have been updated
to the 3 main table.

the final call is up to you.
 

ElizabethTaylor

New member
Local time
Today, 18:43
Joined
Mar 15, 2022
Messages
28
Oh I now understand. I didn't realize it affects all tables. I am learning something new now. Thanks. I will use your structure the way it is. Thanks so much
 

isladogs

MVP / VIP
Local time
Today, 15:43
Joined
Jan 14, 2017
Messages
18,186
yes it is, it is Enforced on the 3 main tables involved.
and not on Teacher, year, terms, stream, grades, students.

imagine you delete a year from year table, then it will delete All
records for that year on the 3 main table?

also you update the year in year table, and all years have been updated
to the 3 main table.

the final call is up to you.

The above statement isn't correct.
Using the relationships as shown in post #15, deleting a record from any/all of e.g. tables Teachers or tblYears or tblStudents WILL leave orphaned records on the 3 'main tables' as @arnelgp calls them. Try it and see for yourself

BTW notice that arnelgp reversed the flow direction on those 3 tables from his original example file in post #8

For a detailed discussion on relationships and referential integrity, see my extended article:

I'd also recommend you avoid using subdatasheets as:
a) these are loaded in addition to the main datasheet thus making form loading slower
b) they often cause confusion for end users
 

ElizabethTaylor

New member
Local time
Today, 18:43
Joined
Mar 15, 2022
Messages
28
@isladogs I guess I will enforce referential integrity. The use of subdatasheets is the best to enforce the right relationships. I have no problem with @arnelgp table structure. I will only enforce referential integrity
 

Users who are viewing this thread

Top Bottom