Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design Question (Acc 2k2 (as 2k) SP2 DAO)

    Hi All,
    I've got a CaseNumberData table. The CaseNumber refers to the basic data for a series of tests done for a set of clients. (There are also LabData tables as well as the ClientInfo tables in this db.) A RequestingAgency (that has requested this testing) is related to this table. This Agency will also have contacts, 1 certainly for the CaseNumber and maybe another for billing. Also the Agency may have different contacts for different requests, as well as they 'may' have different contacts for the billing portion.
    My question is this: How do I design this so an AgencyContact that is associated with a certain CaseNumber will remain assigned to that CaseNumber and not just assigned to the RequestingAgency in general? I've thought of using (and am trying, but am having difficulties doing) a 'join' table between the CaseNumberData table and the RequestingAgency and the AgencyContact (all 3 feeding the 'join' table as it were), but I'd really like to keep the AgencyContacts related to only the RequestingAgency's table. Especially since I'd like to use the NotInList event to allow users to add new Contacts for the Agency.
    If I've not made myself clear enough (which is most likely) please don't hesitate to ask for more information.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Design Question (Acc 2k2 (as 2k) SP2 DAO)

    One approach (which I have used for situations that sound similar) is to have in the CaseNumberData table two foreigh keys, one to the Referring agency and another to the Agency contact.

    Design the forms so that the Agency is selected first, then a contact is selected, using a combo box that only shows contacts from that agency.

    This would also allow you to use a notinlist event.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Question (Acc 2k2 (as 2k) SP2 DAO)

    HI John and Thanks,
    I kinda had it (to a certain extent anyway) designed that way to begin with but then tried to join the Contacts into the Agency information table. That's when I saw that all I had was a Contact that was always joined to the Agency and that way didn't allow for any history, so that if the Contact left the Agency we wouldn't have any record of what that particular contact had done before a new Contact was selected. That's when I jumped to a "join" table (or details table) and got myself confused. Your suggested way makes much more sense. For me it was kinda like the forests and trees thing.
    Thanks again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Design Question (Acc 2k2 (as 2k) SP2 DAO)

    If you want to keep a history you may need a more complicated solution.
    Having an ContactID in the CaseNumber table allows for just one contact. When you change it the old one is lost.

    If you want to know who the contact used to be, you would need a separate CaseNumberContact table, with perhaps start and finish dates.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Question (Acc 2k2 (as 2k) SP2 DAO)

    Hi John,
    Sorry it took so long to get back.
    What I ended up doing is keeping the *Details* table (with the CaseNumberData fk, the RequestingAgency fk and the AgencyContact fk) and keeping track of everything that way. My problem was that I was using a combobox (AgencyPKID in column1) to enter an Agency name (and if necessary adding a new one through the NotInList) AND_THEN trying to add the Contact through either the Agency AddNew or the Agency EditInfo form.
    Couldn't get the relationships to stretch that far. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Anyway, my history remains intact now as a certain Contact is related to a certain Agency per a certain Case Number. I can live with this.
    Thank you for your help.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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