locked
Many to Many relationship in Access Web App RRS feed

  • Question

  • I need to do a many to many relationship in my first Access 2013 web app.

    I have a Parents table and a Students table. Each student can have two parents and each parent can have more than one student in the school.

    Thinking like a seasoned Access desktop developer I took the usual route of creating a parents_students table to link the other two then hit a wall. How do I make the view show the relationship?

    I found this topic: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ccda03e3-a57b-4128-be72-f469c8ec30af/access-2013-web-app-handling-many-to-many-relationships?forum=accessdev

    When I tried it, it is not doing what I want, or I am doing something wrong.

    Seems like a very fundamental thing to be able to do.

    Sunday, January 24, 2016 9:06 PM

Answers

  • Hi Brad,

    To design a many to many  relationship in Access 2013 web app, we can create an third table to save the relationship so that we convert the relationship from "many-to-many" to "one-to-many-to-one". For example, we can create table ParentStudent which have fields below:

    ID: AutoNumber

    Student: Lookup the name of student table

    Parent: lookup the name of parent table

    Then we can create the parent and student recorders then bind the relationship by the ParentStudent table. Here is an figure for your reference:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 25, 2016 6:35 AM
  • Hi Brad,

    There is no necessary to filter the results in "OnLoad" event since we bind the sub view based on the master/child fields. The Access 2013 web app will manage this for us automatically.

    Would you mind sharing more detail about the structure of the tables and how you set for the sub views? Here is a figure for the samples works well for me:

    Here is the result:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 2, 2016 3:00 AM

All replies

  • Hi Brad,

    To design a many to many  relationship in Access 2013 web app, we can create an third table to save the relationship so that we convert the relationship from "many-to-many" to "one-to-many-to-one". For example, we can create table ParentStudent which have fields below:

    ID: AutoNumber

    Student: Lookup the name of student table

    Parent: lookup the name of parent table

    Then we can create the parent and student recorders then bind the relationship by the ParentStudent table. Here is an figure for your reference:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 25, 2016 6:35 AM
  • Got it working! Thanks! It's looking like AWA plus some Office Apps extensions will get me where I need to go.

    Another related question. Let's say you are in the Students view and you click one of the parents.  You get the Student_Parent popup. Then you click the parent name and you get the parent popup.

    Is there a way to go straight to the parent popup rather than via the Student_Parent popup?

    Thanks,

    Brad

    Tuesday, January 26, 2016 1:24 AM
  • Hi Brad,

    Did you want to display the parent filed information on the student view instead of via the popup? If I understood correctly, if the fields are less than four( limitation of Related Items Control) this is possible.

    We can create a query which join the "ParentStudent" and "Parent" table and modify the data source of Related Items Control on the Student's list view and choose the fields you want to display on the Related Items Control.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 26, 2016 8:52 AM
  • Actually, I do want it as a popup, but I want the popup to be the parent record, not the parent_student record.

    I tried making the query as you suggest. I like that I can display a few fields like phone and email, but that had the effect of removing the link entirely. Seems you cannot have a popup view if you are linking from a query?

    I then thought maybe I could abandon the popup idea and add a SubView with a List - that would be a slick. Almost works, but of course I get duplicate entries in the list as I do not see a way to add a DISTINCT filter the list.

    I guess I could add a button that would popup the related parent records, but that is not as slick.

    Edit: I thought that I could use RequeryRecords in the Onload event of the SubView like: Where [parent_student_lookup].[student]=[students].[student].  But [students].[student] is throwing an Invalid Field Name error. I tried referencing it by view name and field name like [Students List].[FullName] and my View Name and UI element name like [Students List].[FullNameTextBox]. How do I reference the parent table?

    - Brad


    Tuesday, January 26, 2016 6:10 PM
  • Hi Brad,

    We can make the popup show up via checking the "Show All Views". Then we can create a view based on the query we created for the relative item control before.

    Here is a figure for your reference:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 27, 2016 9:23 AM
  • If I select my query as the Data Source, the Show All Views option disappears. Actually, I did something and now the Show All Views option has disappeared entirely.

    But, I now like the idea of having the a SubView ListView right on my view. So, how do I filter the SubView ListView? How do I reference the parent table?

    Thanks for your help so far!

    Brad

    Wednesday, January 27, 2016 5:49 PM
  • Hi Brad,

    >>But, I now like the idea of having the a SubView ListView right on my view. So, how do I filter the SubView ListView? How do I reference the parent table?

    Did you want to add the sub view of( ParentStudent table) on the student's "List View" and show the data relative to the student?

    If I understood correctly, we only need to set the link master/child fields for the sub view control. For example, the my Student field in ParentStudent table lookup the name field of Student table. Then I set the relationship like figure below:
     

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 1, 2016 7:50 AM
  • Sorry, that it what I was doing, but I am getting duplicate Parent values. This is my screenshot:

    I would like to know how to filter the results. I mentioned my failed attempts to filter in an earlier response. I can't seem to get a reference to the parent record's ID property. I keep getting errors like "Invalid field name 'Students List.IDTextBox'."

    Thanks,

    Brad

    Monday, February 1, 2016 5:38 PM
  • Hi Brad,

    There is no necessary to filter the results in "OnLoad" event since we bind the sub view based on the master/child fields. The Access 2013 web app will manage this for us automatically.

    Would you mind sharing more detail about the structure of the tables and how you set for the sub views? Here is a figure for the samples works well for me:

    Here is the result:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 2, 2016 3:00 AM