Multiple Table Query Is Blank

[OverMyHead]

I am working on a database for a school scholarship program. There is a main
table with student information (name, address, etc.) with the student ID# as
the primary key. There are many other tables setup to record points for
different objectives. The student ID# is the primary key for each as well.
A student will only appear once per table. I have a query set to find the
total points for each student for each table; however, it is common for a
student to yet receive points in every table due to being new to the
scholarship program. When this happens, the student does not appear in the
query at all - even if they have points in other tables. Can this be fixed?

 

[John W. Vinson]

I am working on a database for a school scholarship program. There is a main
table with student information (name, address, etc.) with the student ID# as
the primary key. There are many other tables setup to record points for
different objectives. The student ID# is the primary key for each as well.
A student will only appear once per table. I have a query set to find the
total points for each student for each table; however, it is common for a
student to yet receive points in every table due to being new to the
scholarship program. When this happens, the student does not appear in the
query at all - even if they have points in other tables. Can this be fixed?

Yes, by correcting your table design. A separate table for each objective is
not a good design - storing data (an objective) in a tablename is a bad way to
go, for the exact reason you're seeing.

It sounds like a classic many to many relationship: each student has zero, one
or many objectives (and points for those objectives), and each objective can
be met by zero, one or many students. A proper design for this uses three
tables:

Students
StudentID
LastName
FirstName
<other biographical info>

Objectives
ObjectiveID
Description
<other info, e.g. maximum points allowed>

ObjectivesMet
StudentID <link to Students, who met the objective>
ObjectiveID <link to Objectives, what did she accomplish>
Points

A very simple Totals query joining all three tables will let you sum the
points per student.

With your current design you will need a Left Outer Join on the query linking
the student table to all of the one-to-one related objective tables. Select
the join line and choose option 2 (or 3) - "Show all records in Students and
matching records in OtherTableName". You'll need some NZ() functions to sum
the points, it'll be pretty complicated, and certainly inefficient.

You can migrate the points from your multiple "objectives" tables into the
normalized table using either a bunch of append queries, or a "Normalizing
Union Query". This may be a bit complicated because you'll need to look up the
ObjectiveID for each Objectives table, either in the writing of the query or
by joining it to the Objectives table.