Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2015
    Posts
    4

    Answered: Multiple many to many relationships

    I have 3 tables, one has the data of students (table named Students), a second one has the data of universities (table named University) and a third one contains documents (table named Documents). The documents are those required by a student to enroll to a specific university, things like ID, photographs and other legal papers.
    I connected the tables University and Document using a helper table called UxD (University and Document) since multiple universities might require the same Document.
    I connected Student with University using just the University ID as a field in the table Student.

    Now I want to create a way that each Student can use checkboxes to show which documents they already have.

    The method I came up with is as follows:
    I created a new table called SxD (Student and Document) which has a primary key that consists of 2 IDs (Student and Document), a field that contains the University ID and one more field as a checkbox to show if the Document has been deposited already or not.

    Every time the user selects a University from a dropdown list on the form that shows Student details, I wrote code that checks if the appropriate entries exist on the table SxD and if not I create them.
    When the user enters the Documents tab, it has a subform, that takes the UserID of the current selected user, the University ID that exists on that Student entry (their selected university) and the subform displays a list of all Documents with a checkbox next to them.
    The system worked well and each Student can select which Documents they already deposited.

    However, there is one big problem with it. If a new Document is added on a specific university, or if a Document is removed, then the subform is not updated. One way to solve this is to add code, everytime there is a new Document added, or a Document removed, code will run and update the SxD table accordingly. However, that might make the whole thing way too slow, or not? The table SxD can get very large there are a lot of universities, and a lot of documents.

    One other way I tried "solving" my problem was using an outer join. But I found out that outer joins produce non-upgradable record sets. While I can see the proper checkboxes on each Student I can't click on them. Any ideas? Is code my only hope? Is it slow?

  2. Best Answer
    Posted by healdem

    "No check boxes....
    So its a user interface issue, not necessarily a table design issue....
    Bear in mind Access is really a front end design tool so it's design can limit what you can do.
    For me id either use
    a pair of list or combo boxes one contains the documents required for a university, the other contains the documents required fir the university held by that student. Clicking on one row in one box moves it to the other box similar to the approach used in the report designer
    Or use an embedded sub form with a combo box on each row containing the documents required by the university. You may need some count in the footer to show how many required documents are outstanding ir satisfied.

    Of the two approaches id use the list/combo box approach. But its not for the feint hearted. It involves a fair bit of coding and a good understanding if the Access VBA object and event models"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So
    a student has documents
    a university requires documents
    that means you need:-
    an entity for students
    an entity to universities
    an entity that identifies what documents are need for what universities
    an entity that identifies what documents a student has

    many to may relatiosnhips in a RDBMS are usually implemented by an intersection table. ferinstnace customer orders.... you have a table for customers, a table for products, a table for orders (which has the customer ID as a FK to customers), and you'd probably have an order details table which has the order number FK to orders table, the products ordered (fk to product table). the orders detail is an intersection table that has the primary key of its parent tables AND anythign else pertinent to that intersection (eg qty ordered, agreed price etc).
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Apr 2015
    Posts
    4
    When I wrote "helper table called UxD" I meant an intersection table, I just forgot the actual word for it.

    I have the 3 entities for the tables and 2 interest tables. One that shows which Documents are needed for which University and one that shows which Documents a Student has. The problem is if I add a new Document to a University, the table that connects Student and Document won't be updated automatically. Which causes the problem of no new checkboxes appearing for the Student so I can check them.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No check boxes....
    So its a user interface issue, not necessarily a table design issue....
    Bear in mind Access is really a front end design tool so it's design can limit what you can do.
    For me id either use
    a pair of list or combo boxes one contains the documents required for a university, the other contains the documents required fir the university held by that student. Clicking on one row in one box moves it to the other box similar to the approach used in the report designer
    Or use an embedded sub form with a combo box on each row containing the documents required by the university. You may need some count in the footer to show how many required documents are outstanding ir satisfied.

    Of the two approaches id use the list/combo box approach. But its not for the feint hearted. It involves a fair bit of coding and a good understanding if the Access VBA object and event models
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Apr 2015
    Posts
    4
    Thank you for the advice. I moved away of checkboxes as they are a pain to deal with in my situation. In the end I settled with listboxes and buttons to move documents from one listbox to the other (add/remove documents).
    And here I was thinking of doing a many-to-many relationship between my intersection table and the student table. Madness!
    It's one of the those things, the problem seems complicated but the actual solution is much simpler.

Posting Permissions

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