Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2015
    Posts
    6

    Question Unanswered: Using a table as a 'dictionary' to numerically define strings

    Hi all,

    I've got a problem which is probably quite an easy one to fix or at least suggest some ideas for.

    I'm a teacher working with National Curriculum levels - students get their results in the form of 3a, 5c etc. (so a string)

    In order to analyse these (averages, weightings etc.) I therefore obviously need to assign these a numerical value to tell the db which is worth more than which.

    I've therefore made a simple table with each grade possible and it's numerical equivalence. My question is, is there a way to easily collect each student's numerical performance in EACH SKILL.

    For example, if a student has different results in Listening, Reading, Writing and Speaking, how can I use this one table to look up a numerical value for each separately? I've tried using a select query with an inner join between the main results table and the equivalences table, but this falls down when I need to use the same equivalence table multiple times in the same query to find the numerical equivalence of each separate grade.

    The only solution I've found is to query each skill separately (so 15 queries in all!) and then make a final query which takes just the numerical output from each, so that these are all in one place together. I can then use recordset loops etc. to do the calculations I need to do. This does work, but I can't help feeling there should be an easier way to do this which is more efficient... Maybe using a union query? I don't know enough about them to know if that is the way forward, really.

    Thanks so much for any thoughts or ideas you might have - I'm learning a lot from you guys so thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without knowing your table design this sort of question is impossible to answer

    is it possible, almost certainly
    is it possible with your current design who knows.
    ..but it should be dooable with a good normalised table design
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2015
    Posts
    6
    So if you imagine there are 2 tables:

    tblresults:
    StudentName, ListeningGrade, ReadingGrade, WritingGrade, SpeakingGrade

    tblgradeval:
    Grade (string) , Gradevalue integer


    The query then outputs:
    Gradevalue(Listening), Gradevalue(Reading), Gradevalue(Speaking), Gradevalue(Writing)

    for each Studentname in tblresults.

    Does that make sense?

    Many thanks again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Does it make sense, well sort of. Its bad design, its not normalised design. In an ideal world you'd wouldnt have the grades in the same table as the student.. but no matter im guessing this is a fairly static requirement so the inherent flexibility of the normalised design isnt needed.

    One of the problems of access is tgat its join syntax is 'peculiar', its access specific.
    But what you should be able to us do a left join on each of the grades in tblresults to tblgradeval.
    In standard SQL that would be something like
    Code:
    select studentname, listeninggrade, l.value as LVal, r.value as RValue, w.value as WValue, s.value as SValue from tblresults as a
    Left join tblgradeval as l on a.listeninggrade = l.grade
    Left join tblgradeval as r on a.readinggrade = r.grade
    Left join tblgradeval as w on a.writinggrade = w.grade
    Left join tblgradeval as s on a.speakinggrade = s.grade
    Order by studentname
    You may want to use the nz function to provide a value for the l., r., w. And s.values
    eg:-
    Code:
    select studentname, listeninggrade, nz(l.value,0) as LVal, nz(r.value,0) as RValue, nz(w.value,0) as WValue, nz(s.value,0) as SValue from tblresults as a
    ...but thats not the Access way of doing joins
    You should be able to do this using the query designer, but the following may be wrong as i tend not to use the designer
    Add the tblresult once and add tblvalue 4 times
    Then click the listeninggrade and drag it to one of the tblvaluse tables
    Select the line between the two tables and define the link as you want all values from tblresults and those that match from tblvalue. I dont have a copy of access here so although the above wording is wrong it should point you in the write direction
    Repeat for the other 3 grades in results each to a copy of tblvalue
    Specify the sort order on student name.

    The reason for using a left join is that if the sql engine cannot find a matching value for anyone of the grades the student name will always be returned with null values in the missing matches. If you use the nz function and supply, say 0 you will always have a value for each grade
    Last edited by healdem; 08-25-16 at 12:13.
    I'd rather be riding on the Tiger 800 or the Norton

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
  •