Database for class tests

[Ramesh]

HI,

I d like to setup a database for storing test scores for a class. A class
of 25 will take around 20 tests in 6 subjects. I d like to have a form with
all the names displayed for the test scores to be entered.

Could someone give me an idea of how to design the tables for this? I had
created a table for StudentInfo, TestInfo and TestScores. But I dont know
how to get all the studentnames displayed on the form instead of selecting
them from the list. The reason is I dont want any of the names left out
when entering.

Thanks
Ramesh

 

[John Spencer]

You can create a record in test scores for each Student and Test
combination.

Since you posted no information about your current table structure the
following is a wag.

INSERT INTO TestScores (StudentID, TestId)
SELECT StudentInfo.StudentID, TestInfo.TestID
FROM StudentInfo, TestInfo

That should create 500 records (25 * 20) in the TestScores table.
If you wanted to just add one test you would add a where clause to
specify the one test.

When you want to enter data for a particular test, you will just filter
a query that looks something like the following as the source for your
entry form.

SELECT StudentInfo.StudentName, TestScores.Score
FROM StudentInfo INNER JOIN TestScores
ON StudentInfo.StudentID = TestScores.StudentID
WHERE TestScores.TestID = "Math1"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ramesh]

That s it, John Spencer. Great. Thanks.

I have not completed the work. But sure looks like your pointer would do
the job.

Thanks again.
Ramesh