Solved Teachers database RELATIONSHIPS

[ElizabethTaylor]

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.

 

[Uncle Gizmo]

You might find my blog here useful:-

Many to Many Relationship - Nifty Access

Many to Many Relationship - 3 Video's In their simplest form Many to Many relationships are just two columns in a table. The first column representing one side of the Many to Many Relationship and the other column representing the other side of the many to many Relationship. In this particular...

www.niftyaccess.com

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]

Hmm, you are missing some Junction tables. Perhaps this will help...

Data Model - Training with Attendance

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[plog]

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]

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]

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]

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]

another demo, i made "new" tables to hold some informations.

 

[ElizabethTaylor]

Thanks everyone for your contribution. @arnelgp I really appreciate.

 

[ElizabethTaylor]

another demo, i made "new" tables to hold some informations.

@arnelgp you should be paid for what you have done. You have gone an extra mile. You are a blessing in this forum.

 

[ElizabethTaylor]

Thanks once again

 

[Pat Hartman]

Nice example @arnelgp

 

[ElizabethTaylor]

@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]

@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.

 

[arnelgp]

you do not need to Enforce Referential integrety to the other table.
setting it to the 3 table is enough.

 

[ElizabethTaylor]

I think it is good to enforce referential integrity to prevent orphan records. I guess it won't interfere with the table structure. @mike60smart and @arnelgp thanks so much.

 

[arnelgp]

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]

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]

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:

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

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]

@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