Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: record restriction

    my sports day database for my school assignment is finished... well, so i thought. when putting in some test event results i noticed that it would be possible to have two students receive 'first place' in the same event, and for a student to get two sets of results for the same event.

    my table setup:
    tblHouses -> tblStudents -> tblResults <- tblEvents <- tblEventTitle

    basically, student data is stored in tblStudents, each event is in tblEvents, and the results are in tblResults.

    a student can earn 8 points for first place, 7 points for second, etc all the way down to 1 point. ANY student who participates in an event earns at least one point.

    is there any way that i can have Access check if a student's ID number already has a record for a certain event ID number, before results are entered into tblResults?

    is there any way to check of a certain event ID number already has a record for 8 points, 7 points, 6 points, etc?

    thanks a lot.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Greg

    This sounds like a great example for a discussion of Primary Keys, Alternate Keys and Candidate Keys (hey - stop groaning at the back).

    When designing a table you will identify sets of field(s) that uniquely identify a record. These should be as small a number of fields as required so you don't count all the fields in the table as one set - that would be cheating.

    All the sets of fields you identify are known as Candidate Keys - they are all candidates for the Primary Key. The Primary Key is just that- in the Keys world it is the Big Cheese, the Head Honcho, the Main Man, the Big Cahoona etc etc. (Airplane fan anyone? No?). It is used to identify records in your table and relate your table to other tables. Typically, it is the narrowest (least number of coumns) and contains the columns whose data is least likely to change (immutable). The other Candidate Keys are not just discarded - they are implemented in your database as Alternate Keys (as a unique constraint).

    This preamble takes us to your problem - you have a table like this (with presumably other columns):

    TblResults
    ----------------
    StudentID
    EventID
    Place

    Your candidate keys are:
    EDIT - removed the punchline when I reread this is for an assignment. You'll need to identify your own candidate keys

    So - chose one to be your primary key and put a unique index on the other. I imagine you know which should be which?

    More exciting info on the world of Relational Database Design:
    The Link

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Poots:

    Alternate Keys? Candidate Keys? I've heard of Primary (compound or otherwise) and Foreign ... Some kinda new fangled DB teminology?

    ... Or I must be getting old ...

    EDIT: Ahhhhh ... New fangled ... What I know as "attributes" ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've never read <deep echoey voice>The Link</deep echoey voice>?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2006
    Posts
    10
    thanks a lot pootle flump.
    i will have a look at my tables and see if i can get things working as per your advice

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    You've never read <deep echoey voice>The Link</deep echoey voice>?
    What? I guess not ... I guess I must be Missing somthing ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2006
    Posts
    10
    okay, i successfully confused myself

    i tried to set 'indexed (no duplicates)' on the 'studentID' and 'points' fields in the results table, but the problem is that there are many events and a student can enter as many events as they wish. as there are many events, there will be a first place, second place, etc. (ie. 8 points, 7 points allocated, blah blah) for each event... so therefore Access gets cranky and spits out an error message about duplicate records.

    have i missed something here?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by greg277
    okay, i successfully confused myself

    i tried to set 'indexed (no duplicates)' on the 'studentID' and 'points' fields in the results table, but the problem is that there are many events and a student can enter as many events as they wish. as there are many events, there will be a first place, second place, etc. (ie. 8 points, 7 points allocated, blah blah) for each event... so therefore Access gets cranky and spits out an error message about duplicate records.

    have i missed something here?
    What is your primary key again? Since you set the StudentID to "no dups" it is correct to not allow that value in the column again ... Revisit the purpose of what a primary key is for and what it does ... Heck, why don't you tell me and all of us here to remind us too?
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Sep 2006
    Posts
    10
    in the Results table the primary key is just an autonumber (hence its different for each record of results, and uniquely identifies each record ).
    but my problem is that a studentID can be entered more than once for the same eventID in the results table; neither studentID or eventID is the primary key for tblResults, but i need to set it so that studentID is unique for each eventID - is that even possible?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by greg277
    in the Results table the primary key is just an autonumber (hence its different for each record of results, and uniquely identifies each record ).
    but my problem is that a studentID can be entered more than once for the same eventID in the results table; neither studentID or eventID is the primary key for tblResults, but i need to set it so that studentID is unique for each eventID - is that even possible?
    Read your last statement VERY CAREFULLY ... It sounds like you're wanting directly contradictory attributes ... Specifically: "a studentID can be entered more than once for the same eventID in the results table" whereas "but i need to set it so that studentID is unique for each eventID" ... Can I assume in the results table? You can design the results table to have the latter happen ... But how?????
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by greg277
    in the Results table the primary key is just an autonumber (hence its different for each record of results, and uniquely identifies each record ).
    Aha - a Surrogate Key! Without wanting to get into the ins and outs of surrogates - I never consider surrogotes when designing my database (the logical design). I consider them an implementation issue. A surrogate does not identify unique records - it identifies records and uniqufies them. You can enter the same data a million times and your surrogate will merrily count up despite your table being full of junk. A unique index on at least one set of columns is virtuaslly mandatory when using a surrogate. So - ignore your surrogate - it has no part to play in your problem.


    Quote Originally Posted by greg277
    but my problem is that a studentID can be entered more than once for the same eventID in the results table
    uhuh EDIT - just read Mike's post - that should have been..... eh?
    Quote Originally Posted by greg277
    neither studentID or eventID is the primary key for tblResults, but i need to set it so that studentID is unique for each eventID - is that even possible?
    Please read my post again and The Link.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2006
    Posts
    10
    Quote Originally Posted by M Owen
    You can design the results table to have the latter happen ... But how?????
    good question, and one to which i haven't an answer
    i think i will sleep on it and try to work out what i am doing tomorrow. thanks for all the posts, guys.

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by greg277
    good question, and one to which i haven't an answer
    i think i will sleep on it and try to work out what i am doing tomorrow. thanks for all the posts, guys.
    Poots and/or I can tell you how in less than a minute but we (or at least I won't - and I'm sure he won't either) cause that's why you're in school ... To learn how to do that yourself ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Sep 2006
    Posts
    10
    success!
    i just created a couple of unique multiple-field indexes in tblResults; one between eventID and points, and another between eventID and studentID. all sorted

    cheers

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by greg277
    success!
    i just created a couple of unique multiple-field indexes in tblResults; one between eventID and points, and another between eventID and studentID. all sorted

    cheers
    FYI: And those multi-field indices are called compound keys (classical) ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •