Assistance in defining relationship between tables

[Matt]

I am a relative newbie to Access DB design and am looking for some assistance
in setting up appropriate relationships between tables so that I can run
queries and report against them. The scenario involves student information
and standardized testing reults from a variety of testing sources and years.
I have been able to get all of my data imported from flat files and allocated
to tables based on their originating source. The Tables contain the
following info:

Student Info:
Student ID
Last Name
First Name
Address
Various Demographic Info

Testing Source 1
Student ID
Last Name
First Name
Test Result 1
Test Result 2
Test Result 3...

Testing Source 2
Student ID
Last Name
First Name
Test Result 1
Test Result 2
Test Result 3...

Testing Source 3
Student ID
Last Name
First Name
Test Result 1
Test Result 2
Test Result 3...

etc.

I have Student ID set as the primary key for the Student info table and an
access assigned autonumber primary key for all of the other tables.

Intuitively, I would think that the relationship should be defined with the
student ID. It is the common value for all tables.

I have tried defining this a number of ways:

-Student ID related to student ID for all all of the testing tables
-Student ID related to Student ID for all tables

I am initially trying to run a query which will report on a subset of test
results for all students. For Example

Last Name - First Name - Student ID - Test Source 1.test1 - test source
2.test1

when I run the query, I get no results.

Any assistance you can provide is greatly appreciated!

Matt

 

[tina]

first, you need to normalize your tables design. a valid design would be

tblStudents
StudentID (primary key)
LastName
FirstName
Street1
Street2
City
State
Zip
< other data that describes a specific student. make sure that each field is
"atomic", that is, one piece of data per field. also note that you should
not put spaces in names, or any "special characters" - only alpha
characters, number characters if absolutely necessary, and underscores if
desired. >

tblSources
SourceID (primary key)
SourceName
< this is a simple list of testing sources. >

tblTestResults
TestID (primary key)
StudentID (foreign key from tblStudents)
SourceID (foreign key from tblSources)
TestDate
TestNumber (1, 2, 3, etc.)
TestResult

the relationships are
tblStudents.StudentID 1:n tblTestResults.StudentID
tblSources.SourceID 1:n tblTestResults.SourceID

if one student takes 3 tests from one source, 2 tests from another source,
and 4 tests from a third source, then that student will have 9 records in
tblTestResults, one for each test.

i strongly recommend that you read up on data modeling and normalization,
because you need to understand the "why" of the model above, not just the
"what". see http://home.att.net/~california.db/tips.html#aTip1 for more
information.

hth