1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

DMax (Applied GCE: Unit 7 - June 2011 Solution)

Discussion in 'Computing and ICT' started by 123sch, Mar 13, 2012.

  1. <font face="Calibri">Dear All</font><font face="Calibri">I'm preparing my candidates for the Applied GCE Unit 7 summer exam and using June 2011 as a guided mock. I'm using the provided solution as guidance on evidence and &ldquo;preferred&rdquo; approaches. However, there are a couple of things I am unclear on and I was hoping that databases experts or Unit 7 aficionados might be able to help.</font><font face="Calibri">On a registration form, a username is generated (eg. FirstName: Adam, LastName: Bateman, Username: adaba4388), with the numerical value incrementing the maximum existing value in the Username field of a Member table. </font><font face="Calibri">Their example uses the following formula, where newusernumber is generated from a query.</font><font face="Calibri">=Left([firstname],3) & Left([lastname],2) & [newusernumber]</font><font face="Calibri">They also provide the following guidance:</font><font face="Calibri">Candidates generated the primary key using a number of different viable ways including the use of four different generated fields on the form to determine each of the parts which were then concatenated or the use of a query like the example above. DMax was accepted though it was not strictly correct in this instance. </font><font face="Calibri">No problem with their example, or the splitting up in four fields, but my question is, why is DMax &ldquo;not strictly correctly in this instance&rdquo; as stated in their guidance? I&rsquo;d like to give the pupils a range of approaches to encourage them to adapt to the task specified.</font><font face="Calibri">I tried the following and it seems to work, other than being probably more difficult for weaker candidates to get the syntax right in an exam. </font><font face="Calibri">=Left&hellip; & Left&hellip; & DMax("Right([tblMember]![UserName],4)","[tblMember]")+1</font><font face="Calibri">Where am I going wrong? Any help would be gratefully received.</font><font face="Calibri">Also, on Activity 2a, Create an efficient database structure that minimises data duplication, the suggested structure for the relationship of four tables of Member, Film, Review, Payment is:</font><font face="Calibri">1:M (Member &ndash; Review), 1:M (Member &ndash; Payment), (1:M Film &ndash; Review), 1:M (Film &ndash; Payment).</font><font face="Calibri">I have: 1:M (Member &ndash; Review), (1:M Film &ndash; Review), 1:M (Review &ndash; Payment).</font><font face="Calibri">In our design, some pupils came up with the &ldquo;looped&rdquo; design but I advised them against it as I told them it &ldquo;duplicates data unnecessarily&rdquo;. I advise them to avoid loops in databases, from some advice in my dim-and-distant past about referential integrity issues; however, the reason I gave them was about duplicating data. Am I missing something?</font>
    Best Wishes
    sch</font>
     
  2. Hope this is clearer,
    I'm preparing my candidates for the Applied GCE Unit 7 summer exam and using June 2011 as a guided mock. I'm using the provided solution as guidance on evidence and "preferred" approaches. However, there are a couple of things I am unclear on and I was hoping that databases experts or Unit 7 aficionados might be able to help.
    On a registration form, a username is generated (eg. FirstName: Adam, LastName: Bateman, Username: adaba4388), with the numerical value incrementing the maximum existing value in the Username field of a Member table.
    Their example uses the following formula, where newusernumber is generated from a query.
    =Left([firstname],3) & Left([lastname],2) & [newusernumber]
    They also provide the following guidance:
    Candidates generated the primary key using a number of different viable ways including the use of four different generated fields on the form to determine each of the parts which were then concatenated or the use of a query like the example above. DMax was accepted though it was not strictly correct in this instance.
    No problem with their example, or the splitting up in four fields, but my question is, why is DMax "not strictly correctly in this instance" as stated in their guidance? I'd like to give the pupils a range of approaches to encourage them to adapt to the task specified.
    I tried the following and it seems to work, other than being probably more difficult for weaker candidates to get the syntax right in an exam.
    =Left... & Left... & DMax("Right([tblMember]![UserName],4)","[tblMember]")+1
    Where am I going wrong? Any help would be gratefully received.
    Also, on Activity 2a, Create an efficient database structure that minimises data duplication, the suggested structure for the relationship of four tables of Member, Film, Review, Payment is:
    1:M (Member - Review), 1:M (Member - Payment), (1:M Film - Review), 1:M (Film - Payment).
    I have: 1:M (Member - Review), (1:M Film - Review), 1:M (Review - Payment).
    In our design, some pupils came up with the "looped" design but I advised them against it as I told them it "duplicates data unnecessarily". I advise them to avoid loops in databases, from some advice in my dim-and-distant past about referential integrity issues; however, the reason I gave them was about duplicating data. Am I missing something?
    Again, any help would be gratefully appreciated to get my methodology right for this unit.
    Best Wishes
    sch

     
  3. DEmsley

    DEmsley New commenter

    Depending how the data is indexed (and it is usually indexed on the key field) then DMAX would only work if the students were in alphabetical order of forename.
    For example if youstart entering data as:
    • annfr1
    • zebzi2
    Then enter Jon Smith he would be...
    • jonsm3
    so your data would be stored as...
    • annfr1
    • josmi3
    • zebzi2
    If you then enter Jonathan Smith he would be...
    jonsmi3 ooops; duplicate key.

    Interestingly DMAX is not appropriate this time (jan2012) but is given in the mark scheme!
    Hope that makes sense.


     
  4. Thanks for the help but I'm still confused and I know I'm going to show my ignorance here, but here goes...
    Working on "DMAX would only work if the students were in alphabetical order of forename".
    In this situation, Username is Indexed - Yes(No duplicates) and so is ordered AtoZ. As the first part of Username is the first three letters of forename, Username and forename fields are ordered AtoZ.
    I then removed the relationship and the key applied to the Username field and deleted all but the first three records of adaba4388, adast4495, alagr4375,in that order. DMax, as used in original post, returns 4496.
    I remove all indexing on the table, the fields remains in the same order with DMax still returning 4496.
    I then added two additional records and varied their text and numerical values eg. zzzzz5000 and their order and DMax still worked unrelated to ordering.
    So now I'm confused. Why and how is DMax dependent on any sorting within any fields? It seems to cope with returning the maximum numerical (or text) value independent of the original order of the data. I have done some googling but I still can't seem to find an answer.
    Any help, again, is much welcomed.
    Regards
    sch
     
  5. DEmsley

    DEmsley New commenter

    I need to recheck this then. Interesting. Which access version are you using? Ours is 2007.
     
  6. Not doing this myself - but my thoughts.
    The field for the username is not a numeric field but a text/char field- so you should not really use a MAX on it. You should only use it on a proper numeric field.
    In traditional SQL - you could get weird results because it will try to use the ASCII values of the text - or some other unpredictable thing that will be dependent on implementation. Because you are using an aggregate function on what is a text field - in theory you can't always predict the result. Although - if you have tested it - then in Access - you can now say fairly surely that it does work. It's a bit like doing a SUM on the whole username field - what would that give you?
    Just a thought - never really use Access but was a Sybase/Ingres Dba and certainly wouldn't use a MAX on a char field to increment an integer.
     
  7. We're using 2010 and its saved as an .accdb file.
    Thanks
    sch
     
  8. Thanks Cap'n.
    I guess Access is a law unto itself and best practice is of no concern.
    This is from Microsoft Access Help:
    The DMin and DMax functions return the minimum and maximum values that satisfy criteria. If expr identifies numeric data, the DMin and DMax functions return numeric values. If expr identifies string data, they return the string that is first or last alphabetically.
    You can use the DMin or DMax function in a module or macro or in a calculated control on a form if the field that you need to display is not in the record source on which your form is based.
    Ah, the real world. It's been a long time since I had those "real-world" concerns.
    Regards
    sch
     
  9. Another thought then - because you are taking right[4] what would happen if - there were a string aaaa in one of the fields - would DMAX + 1 give the next highest number of aaab?
    Then - because you have used right 4 when you get to 9999 things would go wrong because it would cat 10000 and then the next time DMAX right 4 would be 0000.
    I'm not saying thats why its not strictly correct - just trying to guess what they might mean.
     

Share This Page