Impossible query

[Gr3g0ry]

RELATIONSHIPS

ACTIVITIES TABLE


In the Activities table, Ledby is a teacher,Assistedby is also a teacher. President, VicePresident, Secretary, PRO & Treasurer are all students. How can i create a query to show all participants in an Activity, be they teachers or students ? i wanna show first name and last name of all participants.

is there a way to do this ?

 

[plog]

Put all your people in 1 table called People. This means you no longer have those 3 tables for people, they all go into this new table.
Strip out all the role named fields in Activities (ledby, assitedby, Secretary, etc.).
Create a new table called Roles with this structure:

Roles
role_id, autonumber, primary key
id_activity, number, foreign key to Activities
id_Person, number, foreign key to People
role_type, text, this will hold all those role values that are currently field names in Activities

Those 4 fields now hold all the information about who goes with an activity and their role. Instead of a field for each role in Activities you will now have a row in Roles for each role.

 

[Gr3g0ry]

Thanks for your feedback. students and teachers cant go together. guardians is not required in the query i wanna create. ill try the new table approach, but is there any way to create a query currently that will show all persons affiliated with the activity

 

[The_Doc_Man]

students and teachers cant go together.

While I believe that you think this to be true, and I'm not saying you are wrong, is there a definite reason you think it is this way? A common problem we have to address is that saying something "can't happen" isn't always 100% accurate. Since we don't know your design and the purpose you want the tables to address, we might not see the reasons.

The reason I ask is because looking at the fields in the two tables Students and Teachers, there is a LOT of overlap. You would have very few fields in either table that did not 1-for-1 overlap. This is USUALLY an indicator of an inefficient design. Understand that we have ALL faced the dilemma of efficiency or doing what the boss said was required. But it helps US to know what constraints you face because that will help us stay closer to your problem.

I see another questionable situation in your diagram. You appear to show the Guardian ID as being linked to the Student ID and yet there is also a Student ID in THAT table and I can't see why you wouldn't have linked the student IDs together. AND you have "Guardian" and "Guardian2" in the table, which is called a "repeating group." This is a normalization error. Your guardian table should bear a 1/many relationship where Student is the 1-side and Guardian is the many-side. Then you have as many guardians listed as you need because 1/many INCLUDES 1/1 and 1/none.

 

[MajP]

Without changing tables. I would do the following
To make it easier create queries for teacher, student that returns Fullname

qryTeachers

select TeacherID, [FirstName] & " " & [LastName] as FullName

do the same for students.
qryStudents

Drop qryTeachers twice into the design view with activites
alias the first qry to LedBy
alias the second qry to AssistedBy

Drop the qryStudents 5 times into design view
Alias them as follows
President
VicePresident
Secretary
PRO
Treasurer

Join the correct alias query by ID.

 

[isladogs]

Using 2 separate tables, you can create a UNION query which would combine the teachers and students results

 

[MajP]

Activities Activities

ID	ActivityName	LedBy	AssistedBy	President	VicePresident	Secretary
1​	Activity A	1​	2​	31​	32​	33​
2​	Activity B	3​	4​	34​	35​	36​
3​	Activity C	5​	6​	37​	38​	39​

Query1 Query1

ActivityName	LedBy	AssistedBy	President	VicePresident	Secretary
Activity A	Bobby Prosacco	Dashawn Mayert	Katarina Kautzer	Allie Auer	Fabian DuBuque
Activity B	Lavonne Feeney	Chanel Lubowitz	Marcos Beier	Madison Greenholt	Walter Ferry
Activity C	Leonora Kozey	Grayson Skiles	Eric Veum	Abel Herman	Jaylen Murphy

 

[Gr3g0ry]

While I believe that you think this to be true, and I'm not saying you are wrong, is there a definite reason you think it is this way? A common problem we have to address is that saying something "can't happen" isn't always 100% accurate. Since we don't know your design and the purpose you want the tables to address, we might not see the reasons.

The reason I ask is because looking at the fields in the two tables Students and Teachers, there is a LOT of overlap. You would have very few fields in either table that did not 1-for-1 overlap. This is USUALLY an indicator of an inefficient design. Understand that we have ALL faced the dilemma of efficiency or doing what the boss said was required. But it helps US to know what constraints you face because that will help us stay closer to your problem.

I see another questionable situation in your diagram. You appear to show the Guardian ID as being linked to the Student ID and yet there is also a Student ID in THAT table and I can't see why you wouldn't have linked the student IDs together. AND you have "Guardian" and "Guardian2" in the table, which is called a "repeating group." This is a normalization error. Your guardian table should bear a 1/many relationship where Student is the 1-side and Guardian is the many-side. Then you have as many guardians listed as you need because 1/many INCLUDES 1/1 and 1/none.

i thank you for your response. i wholeheartedly do. i m trying to help a fried do a project.
here is the outline. the database is supposed to keep track of extra curricular activities/clubs in a school.
there are students and each student has guardians
each activity is led by a teacher and an assistant teacher.
each activity has a president, a vice president and so on

 

[ebs17]

How can i create a query to show all participants in an Activity, be they teachers or students ?

You need to revise your relationship diagram.

First thought: For a relationship with StudentID you need several instances of the Students table for President, VicePresident, Secretary, PRO, Treasurer and actually also for Members. So you have to insert the Students table into the diagram six times, for example. Something will be visible like Students_1, Students_2 etc.
Now you can assign StudentID as the associated primary key from Students to each of the foreign keys mentioned.
This also puts you in a position to subsequently create the desired query on the same basis.

Second thought: The whole thing mutates against the madness. Actually you need a many to many relationship between Activities and Students. Because a student can be the president of one activity and a simple member of another, at least theoretically.

An additional Roles table is linked to the associated link table, which uses the foreign key to indicate which role the student has in an activity. On top of everything, there is a time stamp for validity, because people come and go, change their jobs, etc.

Something similar must then take place on the teacher side. Actually, you could now combine the Teachers and Students tables, as their properties are almost the same. You can also add a feature to differentiate teacher/student.
Last but not least, ActivityName would have to be moved into its own table and inserted into the link table using a foreign key.
So I've obviously moved closer to @plog's suggestion.

I've described this to some extent because I don't have a database with your tables and I don't have a servant who can recreate them for me from the pictures above.

 

[mike60smart]

i thank you for your response. i wholeheartedly do. i m trying to help a fried do a project.
here is the outline. the database is supposed to keep track of extra curricular activities/clubs in a school.
there are students and each student has guardians
each activity is led by a teacher and an assistant teacher.
each activity has a president, a vice president and so on

Can you upload a copy of your database with no confidential data?