Results 1 to 6 of 6
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    storing exam paper details. Not sure of best structure

    I already have an exam database which I use in the school where I teach. It works fine and I am trying to add an extra functionality. (To be able to produce a template in the form of a report for students to fill in how many marks they get in each question and guiding them towards extra support for questions which they didn't get right)



    The problem is as follows:

    There are (given the infiniteness of time) an infinite number of test papers we will use as we use the most up to date ones we can.

    I'd like to store the following in the database.

    Exam paper number (integer) unique (this is actually already stored)
    How many marks each question is out of (integer)
    What topic each question covers (string)
    Where students can access assistance to revisit the topic

    I thought the best layout might be as follows

    TblPaperBreakdown

    PaperNumber
    Q1Marks
    q1TopicID
    Q2Marks
    Q2TopicID
    q3marks
    q3topicID
    (up to a total of 25 questions)

    TblAssistance
    TopicID
    TopicAssistance

    tblTopic
    TopicID
    Topic name

    It seems fine but the first table having so many fields which
    are so similar
    with so many primary keys linked to Topic ID bothers me


    I wondered whether a table for each paper might be more suitable.

    Can anyone advise please?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by andy49 View Post
    It seems fine but the first table having so many fields which are so similar with so many primary keys linked to Topic ID bothers me
    I wondered whether a table for each paper might be more suitable.
    You were right to be bothered. Whenever a table has several fields numbered 1,2,3,....to 25 in your case, it needs a redesign to ensure normalisation.

    I don't think separate tables are needed for each question - that would also prevent normalisation

    Try this

    TblPaperBreakdown
    ID field - autonumber PK
    PaperNumber
    Question - integer datatype
    Marks
    TopicID


    So each paper has up to 25 records (1 for each question)
    If the questions are broken down into 1a,1b,1c etc, each of these would be a separate record

    This structure may affect that needed for the other 2 tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Brilliant. Thanks a million.


    Sent from my iPhone using Tapatalk

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Good luck with your project.
    BTW - I'm assuming the FileAssistance field will be a file path or a webpage path
    Do NOT use an attachment field or your database will bloat dramatically to a size where it becomes unusable
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    It’s most likely to be a string. We have a piece of video software the students can access and it’s of a clip number from there.


    Sent from my iPhone using Tapatalk

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Perfect ....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help for my exam
    By alexfegan123 in forum Reports
    Replies: 1
    Last Post: 04-19-2016, 04:18 PM
  2. Exam in access example...
    By charly.csh in forum Access
    Replies: 1
    Last Post: 12-21-2014, 12:49 PM
  3. Need serious help before a final exam...
    By kduznetsov in forum Access
    Replies: 6
    Last Post: 05-22-2011, 08:29 PM
  4. Access 2007 77-605 exam
    By Sheri Kelly in forum Access
    Replies: 0
    Last Post: 10-01-2009, 02:53 PM
  5. Help me asap... I need this for my exam
    By freelance_jhoe26 in forum Access
    Replies: 1
    Last Post: 04-20-2009, 02:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums