Query to join two tables

[Guest]

I have a table, Student_Info_Core, that's actually linked to an external .xls
file that is updated from our student information system, essentially making
it an up-to-date student listing.

A query ALL_STUDENTS gives me a table Student_ID, Last_Name, First_Name, and
Class. They're pulled straight from Student_Info_Core, and Class is simply
2019 - Student_Info_Core!Grade_Level.

A table YEARBOOK is a single-column isting of the Student_ID numbers that
are on the yearbook staff. Likewise, there are tables NEWSPAPER, TECH, and
other such clubs that I want acknowledged on our student ID cards. A simply
query where, for example, YEARBOOK.StudentID is not null can return a listing
of yearbook students' Student_ID numbers, names, and graduation years.

I want to make a query that I can export and send to my photo ID card
software. The fields will be Student_ID, Last_Name, First_Name, and Special.

I want a query that will run the ALL_STUDENTS query, *but* with an added
column, "Special", that will be null if the student is in no other tables,
"Newspaper" if the student's Student_ID is in the table NEWSPAPER, "Yearbook"
if the student's Student_ID is in the table YEARBOOK, "Newspaper, Yearbook"
if they're in both, and so on.

(Note: I know that this could be done with a boolean grid on a single table,
but I have the restriction of the external file updated from an outside
database to which I have no access other than grabbing xls files!)

Thanks.

Matt Hohmeister
Technology Coordinator
Leon High School
Tallahassee, FL

 

[pietlinden]

I have a table, Student_Info_Core, that's actually linked to an external .xls
file that is updated from our student information system, essentially making
it an up-to-date student listing.

A query ALL_STUDENTS gives me a table Student_ID, Last_Name, First_Name, and
Class. They're pulled straight from Student_Info_Core, and Class is simply
2019 - Student_Info_Core!Grade_Level.

A table YEARBOOK is a single-column isting of the Student_ID numbers that
are on the yearbook staff. Likewise, there are tables NEWSPAPER, TECH, and
other such clubs that I want acknowledged on our student ID cards. A simply
query where, for example, YEARBOOK.StudentID is not null can return a listing
of yearbook students' Student_ID numbers, names, and graduation years.

I want to make a query that I can export and send to my photo ID card
software. The fields will be Student_ID, Last_Name, First_Name, and Special.

I want a query that will run the ALL_STUDENTS query, *but* with an added
column, "Special", that will be null if the student is in no other tables,
"Newspaper" if the student's Student_ID is in the table NEWSPAPER, "Yearbook"
if the student's Student_ID is in the table YEARBOOK, "Newspaper, Yearbook"
if they're in both, and so on.

(Note: I know that this could be done with a boolean grid on a single table,
but I have the restriction of the external file updated from an outside
database to which I have no access other than grabbing xls files!)

Thanks.

Matt Hohmeister
Technology Coordinator
Leon High School
Tallahassee, FL

In short, your design is wrong. (Sorry, too late to beat around the
bush...) Why not just dump all the (Student_IID,Activity) combinations
into a single table? (You can always query to get the individual
groups). "Special": you could do that by outer joining Student to
Activity. (Show all from Student, and then set the criterion for the
Activity.StudentID field to NULL)

You should have 3 tables
Student---(1,M)----Participates----(M,1)-----Activity

One student may participate in zero or more Activities
Each activity may be participated in by many Students.

table1: Student(StudentID (PK), FirstName,...)
table2: Activity(ActivityID (PK), ActivityName)
table3: Participates(StudentID (PK1), ActivityID (PK2))

table3 is the join table between tables1 and 2. (breaks the
many-to-many relationship).

so students who don't participate...

SELECT Student.StudentID, FirstName, LastName
FROM Student LEFT JOIN Participates ON
Student.StudentID=Participates.StudentID
WHERE Participates.StudentID IS NULL;

 

[Guest]

OK, I see what I've been doing wrong. I made separate tables for Student and
Activity

Now, the only thing I need is a way to consolidate those activities. I'm
trying to build a query that will list each student's StudentID, Last_Name,
First_Name, Class, and Activities like this:

Student_ID: 123456789
Last_Name: Smith
First_Name: John
Activities: Newspaper, Yearbook, Tech

Thanks again!