Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    two way table or multi tables

    I have designed my own database to collect student test scores and convert them to a level (numerical)

    The problem is there are a number of tests and each test is different.

    the students sit one of three assessments. Each assessment has its own grade boundaries which are too random to create formulae.



    Click image for larger version. 

Name:	pic1.png 
Views:	53 
Size:	17.0 KB 
ID:	26561



    I'm wondering if it would be more effective to create (in excel - then upload to access) 12 tables each with 100 rows, two fields (score and grade)

    For example
    Lower test1
    lower test2
    lower test3


    medium test1
    medium test2

    and so on
    or whether anyone can think of a more effective idea.

    Its a fairly long term project and any ideas would be greatly appreciated. My main weakness as a novice is that I don't see the full potential

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From what you have provided (not that I fully understand), I would start out with these tables:

    tblStudents
    StudentID_PK Autonumber
    FirstName Text
    MiddleName Text
    LastName Text
    DOB Date/Time (date of birth)
    (other fields related to student... address, phone)

    tblScores
    ScoreID_PK Autonumber
    StudentID_FK Long Integer (link to tblStudents)
    PaperID_FK Long Integer (link to tblPapers)
    TestYear Integer
    pctScore Double (or Integer if score % will always be a whole number)
    Grade Text

    (AssessmentDate ????) (test date??)
    (Not sure where Levels 1-9 fit in)

    tblPapers
    PaperID_PK Autonumber
    PaperDesc Text (Lower, Medium, Higher)

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

    this certainly helps and shows my first effort needed much more thought.

    But it doesn't quite hit the crux of the matter

    my issue really is around how I create the tables to make accessing their final grades easier

    eg

    teachers will fill in the % scores but I'll need a table to convert this to a final Level (9 of these levels)

    so a teacher filling in that a student had got 85% on their test, access needs to be able to get hold of which test they did (I can see that clearly on your plan)

    but then needs to be able to locate what their score represents as a Level?







    because having taken the "testHIGHER1" and got 85% they would get a grade 6+


    (However if theyd taken "testLower1" and got 85% they would get a grade 4- (its an easier paper)

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So........ do you have a piece of paper that has the conversion from "testHIGHER1" - 85% = 6+ ??
    For all of the tests (lower, middle, higher, extension) and for all of the %grades?? (That should be 108 lines --> 9 levels X 4 tests X 3 years)

    Then create another table (tblGrades)(this would be a look up TABLE), write a UDF to convert "testHIGHER1" - 85% to 6+ and enter the grade into the field.

    tblGrades
    GradesID_PK Autonumber
    TestType Text (lower, middle, ....etc)
    pctScore Double (or Integer if score % will always be a whole number)
    Grade Text

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thank you so much.

    So user defined functions here we come. I do indeed have paper which indicates things like

    1-13 = grade 2
    14-20 = grade 3

    Etc

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

    I've written a UDF which has pupils score and test_ID passed to it and it calculates the grade. It works perfectly.

    I'm a little unclear where to go next.

    My tbl_results has the test score and the UDF can convert that to a grade (but I cant put the UDF in a table - is that right?)

    But I'm unclear how to do that with a lookup table? (I thought a lookup table just appeared as a list that you picked from - that's not what I'm after as it's possible to do automatically using the UDF

    Thanks in anticipation

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How do you want to use the UDF?

    You can add the UDF to a query to display the grade.
    You can execute the UDF to enter the grade into the tblScores.Grade field.
    You can use the UDF in an event, say the form before update, to get the grade and enter it into the tblScores.Grade field.


    Would you post your dB? It will be easier to answer questions..... Just need a few records... delete/munge any sensitive data..Compact & Repair..... Zip it

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

    Forgive any poor programming

    lowerexams draft basicforum.zip

    Hope this is of some help

    Staff choose the year they teach then the class and select November (current test)

    They then import the first three results which are totalled. I'd like the UDF to add the grade at the end
    The UDF is Getgrade()


    Many thanks Andy

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Andy,
    I spent a couple of hours looking at the dB. There are several things that I would change/fix, but I don't understand HOW/WHAT the process is.

    1) I don't see the Grades table.
    2) What is "UPN"?
    What is "P1"? (Paper1?)
    What is "P2"? What is "MA"?
    3) The students sit one of three assessments. What is an assessment: a Lower paper, a Medium paper and a Higher paper?
    4) Can a student have an assessment in each Lower, Medium and Higher paper to generate their score? On must the three assessments be for one paper?
    5) You have 590 records in tblSCORES, but there are only 2 records in tbl_students that link to tblSCORES (tbl_students.upn -> tblSCORES.upn).
    6) I see that the field RAW is a calculated value.
    so a teacher filling in that a student had got 85% on their test, access needs to be able to get hold of which test they did
    but then needs to be able to locate what their score represents as a Level?
    because having taken the "testHIGHER1" and got 85% they would get a grade 6+
    There are 3 papers with 9 levels and 3 years? How are they related?

    7) Would you pick one student and walk me through HOW he/she gets a grade?

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

    Happy to Steve. Can I just add your support is great.

    look at this new version where I've "followed" a girl called Mia




    The process should be


    pupil takes specific test
    scores entered
    Level calculated by access UDF
    Grade Looked up from tbl.grade

    (note that level and grade are not the same but a level 3 is always a grade 1+)


    Mia Joy
    UPN = *********** (on this mock up it's A311201408063)

    (unique pupil Number - something similar to A123456789)




    Firstly the tier and year of the assessment that Mia sat:

    As a year 7 student Mia did our 2005 version of the exam (the exams were originally national exams and this is a copy of the one used in 2005)
    As a low level student Mia was expected to get level 3. so she took the 3-5 (lower - this is the tblscores.tier) assessment.


    Her scores were:

    Paper 1 = 24/60
    paper 2 = 13/60
    Mental arithmetic = 9/30

    Total = 46 marks (out of 150)

    therefore:

    Tier = 3-5
    year = 2005
    Total Raw score = 46

    (tblpapers.tstindex = 1)

    As Mia's score was between 29 and 63 she got a level 3

    the UDF returns a 3b (as opposed to a 3c or a 3a) as her score was in the middle third of the range 29-63


    the tbl.grade will eventually return a grade based on that level between 1 and 9

    a level 3b would then return a grade 1+



    Mia will not sit another assessment in until March next year. She'll take the 2009 probably tier 3-5. The boundaries for grades will be different then TSTindex = 9 so the boundaries will change in March for Mia


    The same principal applies to 590 students from Year 7,8 and 9

    The reason there are so many scores is that I had a draft version which did some things and the tbl.scores I copied over.

    hope this clarifies things

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I still have questions, but I'm still working through your last post about Mia so I can ask intelligent questions..

    Take a look at this dB. I've made a lot of changes.... (but still needs work)
    Attached Files Attached Files

  12. #12
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    A lot is an understatement. But very interested. Am I right in thinking the top group would (could?) have the pupils in a specific class (7jk/mm1 is a class of 32 students). And the bottom section would show the grade areas to be filled in by staff?
    Thank you for this

    Andy


    Sent from my iPad using Tapatalk

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Am I right in thinking the top group would (could?) have the pupils in a specific class (7jk/mm1 is a class of 32 students). And the bottom section would show the grade areas to be filled in by staff?
    Yes, that is one (of many) ways it could be set up.


    Is there a meaning for "7jk/mm1"?

  14. #14
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Quote Originally Posted by ssanfu View Post
    Yes, that is one (of many) ways it could be set up.


    Is there a meaning for "7jk/mm1"?
    Only in as much that it's a year 7 student.

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

    grades table

    lowerexams draft basicforum MOD.zip

    Version with grades table.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-09-2015, 06:16 PM
  2. Replies: 5
    Last Post: 04-29-2013, 01:20 PM
  3. Replies: 11
    Last Post: 08-22-2012, 06:34 AM
  4. How to Search Fields from Multi-Tables Form?
    By Yeisha2008 in forum Queries
    Replies: 9
    Last Post: 07-19-2012, 11:41 AM
  5. Using Multi-column look-up tables
    By Reh in forum Database Design
    Replies: 4
    Last Post: 07-30-2011, 01:50 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