Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, I am having a mental block in terms of relations and keys.

    I have a table of advisers, a table of clients and a table of meetingss the advisers have with clients. My thinking is that the imeetings table is a junction between a many to many relation? An adviser can see many clients and that client could see many advisers.

    Assuming I am correct here, my junction table should have it's PK made of the FKs from each of the two outer tables? But this creates duplicates values in the index, is it correct to put a third independent key in to create a three key compound? Again assuming this is correct, when I do my form and subform, with the client table being the main form, and meeting form as the subform, the fk in the sub form is taken from the clientID, how do I get the adviserID key to auto update in the sub record too?

    Many thanks, Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, that is a many-to-many relationship. You can set the primary key of the junction table to the combination of the foreign keys. Each of the foreign keys can occur many times, but the combinations should be unique.

    If you have a main form based on the clients table and a subform based on the meetings table, the subform should have a combo box to select the adviser (with the advisers table as row source).

    See post 364203 for example of how to handle data entry for a many-to-many relationship with a main form and subform.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767541' date='26-Mar-2009 13:49'][/quote]
    Here you have a Client/Adviser sample.
    HTH
    [attachment=83075:db1.zip]
    Attached Files Attached Files
    Francois

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your comprehensive replies, thankfully I had got it right. What it has made me realise however is that the issue I have is removed somewhat.

    My main form is client and sub of meetings. A client is intitially assigned to one adviser and usaully that adviser looks after that client through to the end of the process, which could be many meetings. I therefore want the adviser key in the subform to auto update without having to rely on the data inputter updating. Could I put an adviser field in the client table which can be updated for the intitial assignment when that client record is first logged and use this as a reference to auto update the adviser id in the many form? Or am I bending the rules too much here?


    Thanks, Darren.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I wouldn't do that. You could use code to prompt the user for an adviser when a new client is created, and use that to create the first record in the subform.

Posting Permissions

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