Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Sorting out Query and Database

    I need some serious help. I have a Table that is populated from an Excel sheet that is uploaded as:
    student / course / grade1 / grade 2 / grade3 / final / total
    112--------bio------88-------78--------------80-----B
    122 -------bio------56-------78-------76------65----C
    123--------cal-------90---------------93------------A

    Now I need to run a query that will return all grades final and totals based on
    userid AND course AND that the value is not NULL or empty so if I want to look up 123 I get the following:
    grade1 / grade3 / total---- only since those are the ones that have value on them...
    NOW I thought of the idea of creating a series of table that will sort this problem out as:

    tbl_student
    member_id
    student_id

    tbl_course
    course_id
    course

    tbl_assessment
    assessment_id
    grade1
    grade2
    grade3
    total
    final

    tbl_grade
    student_id---referencing from tbl_student
    course---referencing from tbl_course
    assesment---referencing from tbl_assessment
    grade----grade give to an assessment based on student_id and course

    If this database design works....how can I run a query to extract the date from the table that has all this info?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by beleami
    If this database design works....
    I'm afraid it doesn't

    What you have is a typical spreadsheeet-applied-to-database-thinking problem. With spreadsheets it makes sense for this sort of design. For databases it does not. Your table violates what is called First Normal Form.
    This is a decent introduction to relational database design:
    The Relational Data Model, Normalisation and effective Database Design
    Note especially first normal form. Have a read, come back and suggest a new design for tbl_assessment.

    We can look at your other tables after - there is still some work to do after that. The good news is, once the design is sorted the SQL is trivial.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What is the purpose of the table "tbl_assessment" ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    the tbl_assessments contains the Asst (1-10) Quizzes (1-6) Exams, Midterm, Final, Totals for the students, the reason why this needs to be created is because not every class will make use of every assessment, and they use a combination. For example Bio might have 3 Exams 1 Midterm and 1 Final, while Pathos might have 1 Exam, 3 Papers, and a Final.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, tblAssessment makes perfect sense - but the structure does not. Things get a bit more interesting now we have different assessment types.

    How did you get on with the link?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2009
    Posts
    3
    I think another way to tackle this if it is possible to make TableNames into field under a common TableName For Example

    member_id------class_id-----test------exam------quiz-----final-----total

    Make it into the following:

    member_id------class_id-----assessment
    ------------------------------test
    ------------------------------exam
    ------------------------------quiz
    ------------------------------final

    is this even possible? when uploading from an Excel file where each "Assessment" is it's own column? I hope I am making sense...

Posting Permissions

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