Results 1 to 4 of 4
  1. #1
    Neb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    2

    Question Noob help with table design please

    Hello, and thanks in advance for your time and help.



    It has befallen to me to test struggling students in a high school with several different standardised tests over a period of three or four years. These results are used to track whether they are catching up or falling behind, and to gather evidence for exam boards when applying for 'special access' in exams (Eg. 25% extra time). Up until now the tests have been printed and put in a paper folder. When we want to evaluate a student, we have to put all these sheets of paper onto a tabletop in chronological order, and try to pick out meaning from it. I think a database would serve us better, and would like to be able to maintain a digital student profile with all their historical test data nicely collated and tabulated. The profile would be a screen with the student's information, and then all the standardised tests they have been given over the years grouped into sections. It would be printable.

    The trouble is, I've never really used Access before. I don't know what it can and can't do, nor how it likes to do things.
    I've been watching some videos, reading a lot, and playing a little, but I can't quite get my head around the table design. Here's the 4 tables I have so far:

    tblStudents tblTestType tblMeasure tblResults
    studentID TestID MeasureID ResultID
    FirstName TestName TestID StudentID
    LastName MeasureName Date Of Test
    Date Of Birth MeasureUnit TestID
    Current Age [calc'd] Result
    Cohort

    And here's how some of the data might look when input (tblStudents is obvious, so I have excluded it):

    tblTestType
    TestID TestName
    1 WRAT4
    2 Hedderly
    3 DRA
    4 Dyslexia Portfolio
    5 Hodder
    6 NGRT

    tblMeasure
    MeasureID TestID MeasureName MeasureUnit
    1 1 Single Word Reading Std
    2 1 Sentence Comprehension Std
    3 1 Spelling Std
    4 1 Maths Std
    5 2 Free Writing Speed Words/Minute

    tblResults
    ResultID StudentID Date of Test TestID MeasureID Result
    1 40 10/1/17 1 1 90
    2 40 10/1/17 1 2 83
    3 40 10/1/17 1 3 90
    4 40 10/1/17 1 4 85
    5 22 12/2/17 2 5 20

    Now I need to explain why I have doubts about whether this is the correct table design for my purpose.
    These doubts are based on the ignorance, confusion and misconceptions that I have about queries, forms and reports.

    Before I started to normalise things, I expected to have a table for each test-type, and so the results for one test go in one 'record' (row). Eg.

    tblResultsWRAT
    WRATID StudentID Date Of Test Single Word Reading Sentence Comprehension Spelling Maths
    1 40 10/1/17 90 83 90 85
    2 22 12/2/17 95 100 97 105

    tblResultsHedderly
    HedderlyID StudentID Date Of Test Free Writing Speed
    1 22 12/2/17 90

    I know this approach is not normalised, but it seems more intuitive to me with regard to data entry and retrieval. I can kind-of imagine how to build this input form. Whereas I cannot imagine how I will build the input form from the normalised structure - it seems to me that to input the results from one test (Eg. the WRAT), I would have to do 4 completely separate entries choosing different parameters each time, rather than put all 4 results into one form and click 'done'.

    To list the tests in the 'student profile' screen (a form or report?), the query for the WRAT section of the profile would simply be something like 'list all records from tblResultsWRAT with StudentID 40'.
    Again, I cannot imagine the queries I would need to collate the data using the normalised tables.

    I would be very grateful for any advice anyone can give me at this point to lessen my bewilderment, especially in regard to whether it would be OK to intentionally 'flatten' the table structure.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Noob help with table design please

    But you seem to be spot on. The query designer will help you pull all the info you need

    Either for one pupil or many many.

    I have been developing similar database.

    It does work. Especially with help from the experts on here

    Have you joined your tables yet?




    Sent from my iPhone using Tapatalk

  3. #3
    Neb is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    2
    > Either for one pupil or many many.

    Ah... well I think you just hit upon something that I didn't know how to articulate. When I was asking about 'flattening' the tables, I think what I meant is that I can only conceive of needing to run a query to generate a single profile page or report for one particular student. I don't think I will ever need to run queries that involve comparing data from lots of different students. This is why I'm wondering whether I could intentionally de-normalise them.

    > Have you joined your tables yet

    I haven't started doing anything in Access yet - this is all just in head and on paper at the moment. I tried messing around in Access for a bit but couldn't make headway because it just threw up frustrating errors every time I wanted to try something. So I started watching videos instead. Once I know I have the right idea about table structure, I'll try again and see if I can get a bit further. I guess you're talking about the relationships between tables using foreign keys? I think I understand that aspect well enough. I don't understand 'joining tables' in terms of queries, but that's because I don't understand how Access handles queries yet!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Neb,
    I'm going to suggest that you work through a couple of these tutorials from Rogers Access Library. (About 45 min each)
    You have to work through them but you will learn. The tutorials start with a business description and Problem.
    Then lead you through a process to identify potential tables and fields, and relationships all leading to a database
    design that matches your requirements.

    I would Normalize the tables.

    You should also be familiar with Normalization.

    Entity Relationship Drawing
    Student Class info
    Widgets

    Normalization

    You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

    Good luck.

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

Similar Threads

  1. Replies: 7
    Last Post: 12-06-2016, 09:06 AM
  2. Replies: 2
    Last Post: 06-25-2015, 11:42 AM
  3. NOOB - moving controls in design view
    By brettnolan in forum Access
    Replies: 4
    Last Post: 04-24-2014, 05:37 PM
  4. Noob needs help with form design
    By Motoman in forum Forms
    Replies: 17
    Last Post: 04-03-2014, 04:20 PM
  5. Replies: 1
    Last Post: 01-25-2012, 06:46 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