Results 1 to 4 of 4
  1. #1
    mwatson4788 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    2

    Table relationships - Provider member claim

    I need help on creating the relationships for a new database that includes 3 tables. Specifically I am trying to figure out if there is a many-to-many relationship.
    I have a provider table with a providerId and some fields about the provider. I have a member table with a memberId and information about the member. And I have a
    claims table with claimId, claim date, provider_number and member number. A provider naturally has more than 1 member but a member can also have different claims
    by different providers, is this a many to many relationship and what would the junction table look like?



    Thanks for your help,
    Mark

  2. #2
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    I could be wrong, but maybe:
    Code:
        tblMembers
            MemberID (PK)
        tblProviders
            ProviderID (PK)
        tblProviderMembers
            ProviderMemberID (PK)
            ProviderID
            MemberID
        tblClaims
            ClaimID (PK)
            ProviderMemberID

  3. #3
    mwatson4788 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    2
    Thanks I try this

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A provider naturally has more than 1 member
    I think you have that covered already. But can a member have more than one provider?
    Based on what you say you have, you should be able to retrieve any member claim record along with the member and provider details if your data would look something like this
    tblClaims
    claimId claim date provider_number member number
    1 1 1
    2 1 1
    3 2 1
    4 2 1
    5 2 1
    6 5 1
    The sample data suggests member #1 has several claims with 3 different providers, but does not consider the relationship between provider and claim number. Thus it could possibly require a composite index so that the same claim number could be entered with different providers to allow for the possibility that 2 separate providers could create the same claim reference number (assuming you would not want to repeat a claim/provider combination).

    What I think you might be missing is a table for claim details, assuming one claim can deal with more than one type of service (such as dental, drug, orthopedics, etc.).
    Hopefully, I haven't misunderstood the parameters of the db requirements.
    Last edited by Micron; 04-11-2017 at 02:52 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. provider Microsoft.Jet.OLEDB.4.0 cannot be found... (?)
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 03-07-2017, 01:31 PM
  2. Provider of CurrentProject
    By Joakim N in forum Programming
    Replies: 2
    Last Post: 11-13-2016, 11:53 PM
  3. OleDB Provider is not registered
    By Perceptus in forum Access
    Replies: 1
    Last Post: 01-22-2015, 01:35 PM
  4. How to import data with OLE DB provider for ORACLE?
    By medusa3604 in forum Import/Export Data
    Replies: 3
    Last Post: 12-16-2014, 01:05 PM
  5. Replies: 3
    Last Post: 05-21-2014, 10:15 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