Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: SELECT FROM all tables

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Ideally, the scores of the tests for all students should be stored in a single table. This would be compliant to the Normal Forms (see: An SQL Introduction - Normal Form or Database Normalization Basics).

    If you want to retrieve the list of the tables related to each student you should prefix these tables (e.g. Stud_<StudentName>). That way it would be easier to query the MSysObjects table:
    Code:
    SELECT Name FROM MSysObjects WHERE Type=1 AND Name Like 'Stud_*';
    With your system you'll have to change the query every time you add a table that's not a "student table" to the database.

    Don't forget also that a UNION query is always read-only and presents several other limitations, performances being among them.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    5

    still confused

    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?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    5

    think i got it

    Ok, that makes more sense. I think i got it, thank you very much

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •