Query and related tables

[Guest]

I have 3 tables. Students , Semester 1 and Semester 2
I need a query to list all students meeting a set criteria from table 1
(Studens) and data from either or both of the other 2 tables. The query only
gives me those students who are in all 3 tables. If a student is not in one
of the other 2 related tables (Semester 1 or 2), then he is omitted in the
query results. The only work-around that I can think of is to append the
students to each of the related tables. Is there any other way to do this?

 

[Bill Edwards]

Fix your table design. I am assuming that the Semester 1 table and Semester
2 table contain essentially the same information.
Combine them into a single table; add a field to this table called Semester
and populate this field with an appropriate value.

 

[Duane Hookom]

Most of us would suggest that keeping a table for each Semester is a
mistake. Can't you use one table with a field that identifies the Semester?
If not, you should be able to use a union query to combine the two semester
tables.

 

[Guest]

Hmmm... Here is what I wish to show.

In Student Table - list of students and a field that can be queried that
identifies those with a Reading Intervention

In Semester 1 - Language Arts Course taken and mark received
In Semester 2 - Language Arts Course taken and mark received

My problem is that some were not here for 1st semester, therefore, I only
have data for Semester 2. Those students do not appear.

I see what you are suggesting that I combine the 2 semesters using a Union
query, but I am going to be adding semesters for the following year as
well....and will run into the same problem. I guess if it can't be queried
any other way, I will have to figure out a way to combine all semesters
together.

 

[Duane Hookom]

Bill and I both suggested you use only one table rather than two. The same
is true for using one rather than three or four or five.

 

[Guest]

I think the problem is a little more complex than simply combining the
semester tables into a single table as there is almost certainly a
substantial amount of redundancy which would not be eliminated merely by
doing so.

In the database relational model tables represent entity types and columns
(fields) the attributes of those entity types. Students and Semesters are
entity types. The Students entity type has attributes such as FirstName,
LastName, DateOfBirth etc. The Semesters entity type has attributes such as
Year, SemesterNumber etc. A third entity type would be Courses with
attributes such as CourseName etc.

These three entity type are related by a special type of entity type, a
relationship type, which is modelled by another table. This represents which
courses each student participates in and the semesters in which they do so.
This is thus a ternary relationship. Tables which model relationship types
in this way work by having foreign key columns referencing the primary keys
of the tables between which the relationship exists, so in this case the
table would have columns StudentID, CourseID and SemesterID. As the
combination of values in these columns in any one row must be unique the
three columns can be designated as the tables composite primary key.
MarkReceived is an attribute of the entity type modelled by this table, the
marks apparently being awarded per student per course per semester to judge
by the information you have supplied, so would be a column in this table.

To return rows with the data you require is simply a matter of joining all
four tables in a query and returning the relevant columns from the
appropriate tables.

In future years you simply have to add rows for the new semesters to the
Semesters table, to the Students table for new students and to the Courses
table for any new courses introduced. New rows are added to the fourth
table, lets call it StudentCourses, to represent each student's participation
in the courses as their academic career progresses.

Ken Sheridan
Stafford, England