Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008

    Angry Unanswered: No Idea Where to Go

    Hi All,

    Here's my situation:

    I have one table (tbl_providers) that lists 250 providers each of whom has a unique ID.

    I have a second table (tbl_clients) that lists over 1,000 clients each of whom also has a unique ID.

    Each client can have up to five possible providers (PRV1, PRV2, etc.). The tbl_client reflects this using the unique ID for the provider in one of five fields (PRV1, PRV2, etc.). A provider who is PRV1 for one client can be PRV2 for another client and PRV5 for another and so on.

    On a report, I need to show each Provider only once and all of the clients associated with that provider regardless of whether the provider is their first, second, etc.

    I've tried a few different options in the query but nothing is working for me.

    Please help! Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    have a look at intersection tables
    essentailly a sub table that has a foreign key to both the provider and customer tables
    as to the primary key in the intersection table thats up to you.. normally it would be a composite key made up of the two foreign keys (Provider ID and Customer ID)

    however in this case you may choose to be a bit more creative
    Id suggest you add a third column (say Provider Index, limit it to non null, and in range 1..5, that makes sure you can never have more than 5 providers associated with anyone customer)
    make the customer and provider index as the primary key.. this also allows you to infer preferred suppliers (but I'd be careful of that approach it could trip you up (especially if say provider 1 is originally the preferred supplier and gets demoted.. its a pain to change keys, where as an alternative column (say supplier preference order would design out that issue))

    add a unique index on supplier and provider.. that stops a user associating the same provider and supplier, so you know the same provider cannot be associated with the same supplier more than once

    then when you come to doing your reporting
    you extract all suppliers with the specified provider ID (doing a join on the intersection table, pulling in any provider and customer details as required).
    Last edited by healdem; 08-12-08 at 11:58.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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