Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Unanswered: Validate Data - Please Help

    Hi, I have a table called Table A.

    Appropriate data should be validated before it is accepted in the table:

    1. A teacher must not be deleted if he/she teaches that student.For example, Fionna cannot be deleted because she is Lara's teacher.
    2. A student cannot be added that does not have an existing teacher in the table


    My table looks a bit like this:

    Number / Name / Teacher No

    585 / Lara / 458
    658 / Pater / 288
    584 / Mike / 323
    458 / Fionna / 0
    323/ Tom / 0
    ETC....


    How can I validate them in Access?

    Any help would be appreciated.
    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have a look at relationships.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2008
    Posts
    13
    Quote Originally Posted by StarTrekker
    Have a look at relationships.
    Do you mean to normalise the tables?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nope. Relationships!

    That's where you create links between two or more tables. Pay particular attention to Referential Integrity.

    If you aren't sure where to start looking, you should ask your lecturer / teacher for advice... or get a good book on database design.

    Mind you, learning about normalisation is also a good thing to do if you think you only need one table for this
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it wasn't for the requirement 2
    A student cannot be added that does not have an existing teacher in the table
    then your current design could probably work

    for this assignment you need some mechanism which identifies who is a teacher abd who is not. that could be a separate table containing teachers, it could be a flag in the single table which identifies this person is a teacher.

    if you go down the second route then you will struggle to do what you want with JET (ie automatically .. the db controls the business logic.. you could do it with code. ie before deleting a person check they don't have any "pupils". you could make certain that a teacher is selected in say a combo/list box by pulling in only persons with no taughtby.

    however that could fail, if for example someone can be a teacher and a pupil
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Now this answer is purely with regard to the stated parameters of the assignment, not necessarily the best way to manage this, however the current table structure (plus relevant constraints) should meet all requirements. A teacher is anyone with a related value in the "teacher No" column, no? There's no need for a flag or separate table - it is inferred. Same as an employee-manager adjacency table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...if it was a given that nobody can be a teacher AND a pupil at the same time
    the number in column 3 indicates who is teaching that person, it doesn't handle more than a 2 level hierarchy... but whether thats relevant to the assignment I don't know..

    the single table solution may be way far ahead of the course as it would have to involve sub selects to make sure that the proposed delete would work only if that person number wasn't assigned as a teacher for another person (pupil)

    Im guessing the coursework is looking for a separate table for teacher(s)

    the issue I see with an assumed teacher/pupil relationship (single table as is) is that its possible to add a row, but not add teacher ID, and the model assumes that that person MUST be a teacher.. that will not sit easily with the second requirement.. as is we 'presume' that someone with no teacher must be a student.. but that doesn't stop someone adding a student row. I think to meet the requirement you need something positive to identify who is a student, or conversely who is a teacher
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I agree with you Mark now - I think the assignment probably requires decomposition.

    The below is just playing around with things.
    Quote Originally Posted by healdem
    if it was a given that nobody can be a teacher AND a pupil at the same time
    A pupil has a teacher no. A teacher is someone whose ID appears in another row as a teacher no. Someone that meets both is then both teacher and pupil?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok its probably an extreme example, but if someone is being taught by someone else, and is also teaching someone else, then it is going to be tricky to design a UI for.
    whether such a complication is reasonable given that this is homework rather than real world is a moot point
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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