I'm having trouble structuring my database. It needs to be able to keep track of students personal information, as well as a large variety of test scores. Currently i have a table to keep track of every student's personal information, a table for attendance, and each student has a table to keep track of their scores on each of 100+ tests and the date the test was taken. The tests are taken by each individual student in large groups, so i have test name columns whose records are defined by the date the test was taken. The students do not take the tests on the same date usually. I was hoping to be able to select all students scores for a specific test, among other things. But the number and names of the tables will change, as new students are added. I have
SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0 AND Name <> "CurrentStudents" AND Name <> "Attendance" AND NAME <> "Staff"
That will give me a list of all students that have a table of test scores. Can I use this as an argument in a FROM clause? or loop through it some how and combine with union? or is there just a better way to organize this data? Thanks in advance
having all test data in the same table would put the data in the first normal form, but not the second. to have all test data on the same table would mean each student would have a separate record for each test date. Given the table with the names of all student tables, it should be possible to loop through them and select only information about a specific test. Do i need to switch to vba to do that?
Not necessarily: you can build SQL Statements (Queries) to perform complex selections. The more your database complies with the normal forms (never mind those beyond the third), the easier it is to handle the data using the SQL language. Having a database in the First Normal Form seems kind of mandatory to me.
Test data should probably be in a table with a column containing a foreign key pointing to the Students table so you can create a One (Students) to Many (Tests) relationship.