Many-to-many relationship?

[bilakos93]

Hi all

I have a database that includes the following tables
Students (firstname, lastname, ID, med_check) . Med_check is a date when each student last had a medical check (which is valid for 1 year). It may even be blank
Sport_groups (ID, name, students.ID). Each sport group can contain more than one student and each student may be part of 2 or more groups.
Different sport groups take part in contests on various dates.
I eventually want to identify the students that do not have a recent (less than a year) medical check by entering their sport_groups name and the date of the contest.
The sport_groups name and the date of the contest will be entered each time by the user via a form.
Does it make sense to use those 2 tables?
Given that it is a many to many relationship, should I be using 3 tables instead?

Many thanks

PS
If anyone could give me a hint on the code I'll need to use for the query, that'd be extremely useful!

 

[GaP42]

From your description at least four tables:
Student
- studentID (PK)
- Firstname, & Lastname
- LastMedChkDate

SportsGroup (team/squad ..)
- SportGrpID (PK)
- SportGrpName

SportGrpMbr (a junction table) - the members of the team (do you need to know when they join and leave the team?)
- MemberID (PK)
- SportsGrpID (FK)
- StudentID (FK)

Contest
- ContestID
- SportGrpID
- ContestDate
( -Result
- Opposition?)

If you simply are interested in students that have not had a recent med check then you only really need to look at the student table. If you need to assess this by Sport Group then you could query by SportGrp to identify members who have not had a recent med check.
You may want to identify the students at 11 months after the last med check to find who needs to get one before the 12 months are up.
Notice that the Contest data is not used .. unless you have another need for it, it could be left out. Also Contest does not identify which students played in which contest - which, if required, would imply an additional junction table StudentContest

 

[bilakos93]

Thank you very much

what’s the purpose though of the junction table ? I assume each SportsGroupID will appear once on the SportGrpMbr table?
Otherwise there is a chance that a student will appear twice in the same sportsgroup on different records

i am not interested in details about the contests. Each time there is one , the user will enter the date and the group in a form and it will return those that will need a medical check.

 

[GaP42]

The junction table resolves a M:M relationship (which must be done for a RDBMS to minimise data redundancy) - in your case between Student and SportsGroup. For each sportsGroup a record will be needed for each student who is a member. A student may belong to many SportGroups. So Sports Group ID will appear in the SportGroupMbr table for as many times as there are students who are members of that team - eg 11 team members then 11 records with that SPortGrpID. Set an index on StudentID and SportsGrpID in the junction table - as a unique index - so no duplicates will occur.

You must create the 1:M relationships between the tables in the relationships window to ensure referential integrity rules will be applied.
Are you sure that you need to launch the query to return those who need a check on the basis of the date of the contest? If this is being done in anticipation of the upcoming contest/ event, you could simply have a form in which you enter any date to launch a query to identify any student who has not had a med check within the last "x" days - it does not need the date of the contest, although that may be a date you enter because it is of particular interest.
Relationships
Student 1--=M SportGroupMbr M=--1 SportGrp 1-- = M Contest

 

[The_Doc_Man]

what’s the purpose though of the junction table ? I assume each SportsGroupID will appear once on the SportGrpMbr table?
Otherwise there is a chance that a student will appear twice in the same sportsgroup on different records

Valid concern, but the answer is in the intended usage. Your description included this statement: "Each sport group can contain more than one student and each student may be part of 2 or more groups."

A student can be in many groups and a group can contain many students. This is an example of a many-to-many relationship. Access does not have a formal many-to-many relationship. It only does 1/1 and 1/many and many/1 cases. So a junction table SIMULATES the many/many case. (For what it is worth, very few vendors have an automatically managed many/many case.)

The trick to avoid multiple entries in the junction table is EITHER to declare a unique compound index on the student/group pair (and trap the error if it occurs) OR before you make an entry to the table, run a DCOUNT on the combination to see if a record already exists.

The trick to avoid multiple entries in the "medical check required" list is to use a SELECT DISTINCT clause that doesn't include the group, only the student and the medical check field. If you are not familiar with that method,

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Pat Hartman]

Here is a sample of a m-m relationship. Students-Classes.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[gemma-the-husky]

You would need a junction table if you wanted to store the history of checks for each student.
If you only want to store the latest check, then you can just store that date in the student table, as you are doing.

The medical test history is really nothing to do with the sports group. It's related to the student only.