Single table Design?

[kjmeyer]

I am new to relational database design and have just hit upon the idea that with the project that I am working on I might be better off using one large table (more fields) and not trying to relate multiple tables of data

If I do it this way is it reasonable to use Access to enter data into records using forms and pull data from records using queries if I am using only single table? I also want to be able to print reports and develop more advanced queries as I learn the program

Beyond this Yes or no question let me describe the situation and maybe someone could make a constructive suggestion
I am trying to track results of 8 different tests over a period of 3 years for 800+ students. They all have a unique student ID, first names, last names, etc - one possible table. One group of tests is scored 1-4 (four possible results) - a second possible table) and another group of tests is scored 1.0 - 12.0 (one hundred and 20 possible results) - a third possible table. I am having a hard time thinking of how to relate these tables if they are even conceived correctly in terms of a relational database. I think that each record of a student that includes a unique student ID a semi unique name, should also just include the score for each test taken even though it will not be a unique number.

Thanks for any advic

kjmeye

 

[tina]

suggest 3 tables:

tblStudents
StudentID (primary key)
FirstName
LastName
etc.

tblTests
TestID (primary key)
TestName
TestDescription
etc.

tblStudentTests
StudentID (foreign key from tblStudents)
TestID (foreign key from tblTests)
TestDate
TestScore

if each student will take each test only once, you can create a combination
primary key using StudentID and TestID. if a student may take a test more
than once, you can create a combination primary key using StudentID, TestID
and TestDate. or you can just add an autonumber field to serve as the
primary key in tblStudentTests.

hth

I am new to relational database design and have just hit upon the idea

that with the project that I am working on I might be better off using one
large table (more fields) and not trying to relate multiple tables of data.

If I do it this way is it reasonable to use Access to enter data into

records using forms and pull data from records using queries if I am using
only single table? I also want to be able to print reports and develop more
advanced queries as I learn the program.

Beyond this Yes or no question let me describe the situation and maybe

someone could make a constructive suggestion.

I am trying to track results of 8 different tests over a period of 3 years

for 800+ students. They all have a unique student ID, first names, last
names, etc - one possible table. One group of tests is scored 1-4 (four
possible results) - a second possible table) and another group of tests is
scored 1.0 - 12.0 (one hundred and 20 possible results) - a third possible
table. I am having a hard time thinking of how to relate these tables if
they are even conceived correctly in terms of a relational database. I think
that each record of a student that includes a unique student ID a semi
unique name, should also just include the score for each test taken even
though it will not be a unique number.

 

[kjmeyer]

Great, thanks

Both of you have suggested a third table that I had previously not considered

tblStudentTest
StudentID (foreign key from tblStudents
TestID (foreign key from tblTests
TestScor

That is a table that ties the Student table and the test table together.

I am guessing that there is a way to auto populate the redundant fields (the foreign keys) in this table with the fields of the primary keys, no? and the test score field would be populated via form at the time the tests are scored, yes

Thanks for your help this far and thanks for any further replys. I think I am starting to get my head around this
kjmeyer

 

[tina]

if you set the table relationships in the Relationships window and enforce
referential integrity, you can select tblStudents in the database window and
then AutoForm from the toolbar. Access will then build the form/subform for
you. tblStudents will be the main form - single record default.
tblStudentTests will be the subform, datasheet (multiple records) default.
when you select a student record in the main form and begin entering test
results in the subform, the StudentID foreign key will automatically be
added to each record in tblStudentTests. you'll need to enter the TestID
manually, because of course Access doesn't know what test you're recording
results for. in the subform, you'll want to change the TestID text box to a
combo box so that you can enter the test name instead of the ID number.
you can do various things to gussy up the data entry form (such as setting a
default TestID at each session to automatically fill that field in the
subform) according to your needs, but this will give you the bare bones.

hth

Great, thanks.

Both of you have suggested a third table that I had previously not considered:
That is a table that ties the Student table and the test table together.

I am guessing that there is a way to auto populate the redundant fields

(the foreign keys) in this table with the fields of the primary keys, no?
and the test score field would be populated via form at the time the tests
are scored, yes?

Thanks for your help this far and thanks for any further replys. I think

I am starting to get my head around this.