Results 1 to 9 of 9
  1. #1
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36

    One to many relationship failure


    Hi everyone!

    I am trying to create a one to many relationship in Access. Here is my challenge. I have a list of asset managers (the one) in a dedicated table and a list of funds (the many) in another table. Each asset manager may have more than one fund associated with them, hence I decided to separate the two by creating two tables. I have the "SponsorID" as my PK in the asset managers table and I have the "CounterpartyID" as my PK in the funds table. I added in the funds table a field called "SponsorID" to create possibility to link the tables and then I opened the relationship dialog box to constitute the actual link, whereby the field "SponsorID" in the asset managers's table (the one) is linked to the "SponsorID" field in the funds table, which should bring the FK in the funds table. When I run a query to see all funds associated with a given asset managers, I get blank results. I can't figure out why this is the case so any help would be greatly appreciated.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,940
    It sounds as though you have set up the tables and fields correctly and created a link between the right fields but Access will need you to put the matching Id in the funds table for all existing records. AFAIK Access will only fill that data for you when the two tables are used in a main form with sub form and new records are added.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Thank you. So, if I understand that correctly, the link is not visible but would be visible through a form?

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,940
    If you have created the relationship in the Relationships Window then if you create the sub form on the main form using the wizard, Access will assist you in setting the "Link Child Fields" and the "Link Master Fields" properties of the sub form control. If you then create a new record in the sub form, Access with insert into the field assigned as being the Child Link Field, the value of the field in the main table that has been assigned as the Master Link Field.

    Did you understand that you will have to populate all existing sub form table fields. After all, how would Access, I or anyone else be able to know which manager manages which fund.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Yeah, i agree completely. Thing is, if I have to manually input in my funds table the unique ID that Access gives to my Sponsors, then what is the purpose of separating tables? I mean, everytime someone will have to add a new sponsor/fund, he will have to manually input the unique ID. Please tell me I'm wrong!

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,940
    Yes, you are wrong. Please read my last post again.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Well, that's quite possible. Unfortunately I lack the experience you have with Access so I may have missed a point. I have tried setting up forms and I have seen the parent-child relationship you mention created so to say automatically by Access. however, I'm not clear this point of your post:

    "Did you understand that you will have to populate all existing sub form table fields. After all, how would Access, I or anyone else be able to know which manager manages which fund."

    I wonder if you could provide some more details around this.

    Thank you!

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,940
    Quote Originally Posted by gori1084 View Post
    Well, that's quite possible. Unfortunately I lack the experience you have with Access so I may have missed a point. I have tried setting up forms and I have seen the parent-child relationship you mention created so to say automatically by Access. however, I'm not clear this point of your post:

    "Did you understand that you will have to populate all existing sub form table fields. After all, how would Access, I or anyone else be able to know which manager manages which fund."

    I wonder if you could provide some more details around this.

    Thank you!
    As I understand it you have a table of managers (the one side) which has a PK field. Then you have a funds table (the many side) with a FK field which you have used to create the relationship. That FK field would be populated automatically by Access when entering new records in the funds sub form with the PK of the current record in the main form (managers). However, if you have existing records in the funds table where the FK is not yet populated, then only you will now which manger is assigned to a particular fund and therefore which PK value should be entered to create the correct relationship.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Ok perfect, thank you. I think now I understand. So if I populate the funds table through the subform, then the ID of the managers will be assigned automatically to the funds but it's gotta be through the form, otherwise it wouldn't work, and, as you said, this actually makes sense. I think this thread is solved. I'll be back soon with more complex questions. In the meantime, thanks so much for your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type Conversion Failure
    By fpmsi in forum Access
    Replies: 7
    Last Post: 09-22-2011, 11:25 AM
  2. f4 combo box failure
    By djuplina in forum Access
    Replies: 2
    Last Post: 09-19-2011, 01:03 PM
  3. Date Range Failure
    By goodguy in forum Queries
    Replies: 4
    Last Post: 12-30-2010, 10:22 AM
  4. stored procedures failure
    By MrGrinch12 in forum Programming
    Replies: 1
    Last Post: 06-23-2010, 12:54 PM
  5. Duplicate record failure
    By bugchaser in forum Access
    Replies: 5
    Last Post: 05-21-2009, 08:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums