Results 1 to 15 of 15
  1. #1
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10

    Exclamation Credentialing Database Help!!! I am SO Stuck...

    I am creating an insurance credentialing database for my facility that tracks all of the insurance plans our providers are credentialed with. We have 34 providers, 7 locations, and 232 active insurance plans. Each provider is credentialed with different insurance plans at each location. I need to be able to track that in the database. The way I currently have it setup is I have a Provider List TABLE and within that table I have a columns for the provider names, their credentials, and their ID numbers. I also have 7 additional columns for our 7 facilities. Each of those 7 additional columns is labeled with a facility name and is a LOOKUP FIELD that relates back to a separate table with all 232 of our active insurance plans.

    From that TABLE, I created a FORM with each of the providers (34) listed separately. On each provider form I put a TAB CONTROL. Each TAB CONTROL has 7 tabs for each of the facilities. What I wanted to happen with the TAB CONTROL on the form is that it would list out each of the insurance plans that the provider is credentialed with at each facility. I also wanted to make each of the names for the insurance plans a hyperlink that can be clicked on and will open a second form with all of the contact information for that specific insurance plan.

    My roadblock is that I can't seem to figure out how to make what I want to happen with the TAB CONTROL on the form...happen. I actually don't even know if it is possible. Can anyone provide any assistance with this? Or can you tell me if I'm going about this the wrong way and guide me in the right direction?


  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It doesn't sound like your table design is normalized yet.

    So each provider has ONE plan at each location?
    OR each provider has ZERO OR ONE plan at each location?
    OR each provider has MANY plans at each location?

    Is each plan unique to the provider and/or location? Or is one plan used/referred to by other providers too?

  3. #3
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    So each provider has MANY plans at each location. The total number of plans currently active is 232. So each provider can have up to 232 plans attached to them at each location.

    Each plan is not unique to the provider or location. Each provider can practice at each location (7), but they need to be credentialed by each insurance to bill at that location. So it is possible for a provider to be credentialed by a plan, BCBS for the sake of this example, at locations 1-3 and not at locations 4-7. This database needs to capture that for each provider/location. So I need to be able to tell on the database that Provider A is credentialed for plans abcd at location 1, plans drfs at location 2, and so on for all 7 locations.

    And I'm stuck. This might not even be able to work in Access, but I thought I could try.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm sure it'll work in access. It'll just take some learning. I suggest you do some research on many-to-many relationships and junction tables to get you started.

    I have the following table design suggestion based on what you've told us so far:
    Click image for larger version. 

Name:	zzzz.png 
Views:	25 
Size:	11.3 KB 
ID:	35555

    Obviously the tables need more fields but this should give you an idea. Regarding the tab controls, you can set up your UI that way but it may be more efficient/dynamic to take another approach. But first I suggest working out your table structure. Can you post a screenshot of your relationship window and form so far?

  5. #5
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    I sure can. Give me about 45 minutes and I'll be out of this meeting and back in front of the database.

    Also my relationship window looks NOTHING like yours, so I think I'm definitely approaching this from the wrong direction.

    I appreciate your help : -)

  6. #6
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    Here is my relationship window.

    Click image for larger version. 

Name:	Capture4.PNG 
Views:	23 
Size:	144.9 KB 
ID:	35556

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yep, you need work on the tables/relationships.

    Are those MVFs (multi-Value fields) in the tables?? I really hope not.....


    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

    Question is: Did you draw the tables/fields/relationships on paper/cardboard/whiteboard/etc before creating in Access?

    Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp


    Some suggestions:
    --------------------
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.




    Any chance you would post your dB?
    Make a copy of the dB, change any sensitive info, do a "Compact & Repair", then Zip (compress) it.

  8. #8
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    Yes. They are. (Ducks my head in shame lol). Am I not supposed to do that? I've never had to build anything quite so complex (at least it's complex to me) in Access before. Hence my reaching out. And no I didn't draw it out. I kind of jump into things head first and figure it out as I go and ask for help once I can't figure things out and run into a brick wall. I'm guessing that's not the way to go about something like this in Access. My bad. I'm self-teaching myself how to use this program correctly. Thanks for the link. I very clearly need all of the help and resources that I can get (sigh).

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A lot of us learned the same way.

    My first major dB (for a sports event) gave me MAJOR headaches - I must have gone through a family sized bottle of aspirin in 3 days!!
    Then I asked for help from a friend that programmed for a bank. I asked what was wrong and was told "Bad design"; I had "committed spreadsheet". Not a good day.

    MVFs are half a$$ed implemented in Access. great for adding data, not so much for getting data out (ie printing).


    I would suggest drawing out your tables and fields on paper/cardboard/whiteboard (large ones), then try adding data to see if the design works. Once you feel the design works, only then start Access.


    (Using the image in Post #4)
    In naming fields, you have a table for locations, I would have

    tblLocations
    ----------------
    LocationID_PK Autonumber <<-- this is a primary Key field and a number type, indicated by the "ID" and the suffix "_PK". (my style)
    LocationName Text



    tblProviderLocationPlans
    -------------------------
    ProvLocPlanID_PK Autonumber <<-- this is a primary Key field and a number type, indicated by the "ID" and the suffix "_PK". (my style)
    ProviderID_FK Number-Long - <<--Foreign key field linked to Providers table
    LocationID_FK Number-Long - <<--Foreign key field linked to Locations table
    PlanID_FK Number-Long - <<-- Foreign key field linked to Plans table

    No two fields have the same name, you can easily tell which fields are PK/FK fields and how they are related.

  10. #10
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    Thanks so much! I've been banging my head against every solid object I could find since Monday. I'll try your suggestions out. I appreciate the help. I'm positive I'll be back when I run into another roadblock :-)

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I whipped up an example database that kinda sorta models what you've described. I've left out several of the fields you showed in your post #6 as I don't know what they are and this is just an example. But hopefully you can study this and see how things could work for you.

    InsuranceThingy.zip

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @trumirage,

    I agree with the info you have been given and the sample by kd2017. Here is a link for additional info on various Database Planning and Design topics.

    This one "stump the model" highlights/complements the paper/whiteboard scenario by ssanfu.

    Don't be too quick to jump into physical Access. Model, test, revise---then build according to your "blueprint".


    Good luck with your project.

  13. #13
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    @kd2017

    Thank you! I'll get to white board planning and then play around with the database you sent. You've already pushed me 10 steps ahead from where I was with this just yesterday. And I love the file name :-)

  14. #14
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    @orange

    Thanks for the additional info! I will definitely modify my approach and take several steps back and PLAN first. That's very opposite of my usual plan attacking nature, but it's CLEARLY what's best with managing Access.

    Thanks again for the well wishes!

  15. #15
    trumirage is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    10
    ****UPDATE*****

    I took everyone's advise and did some research and some planning and I finally got a product that works and looks pretty good. Lots of thanks to @kd2017 for providing me with a solid foundation to start with. All of you are awesome :-)

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

Similar Threads

  1. Credentialing Database Help!!! I'm Stuck...
    By trumirage in forum Database Design
    Replies: 1
    Last Post: 09-19-2018, 02:20 PM
  2. Replies: 7
    Last Post: 09-10-2018, 09:39 AM
  3. Stuck on complex Quote/ Order Database
    By Mbroeth in forum Database Design
    Replies: 2
    Last Post: 12-11-2017, 07:38 PM
  4. I'm stuck
    By tc197 in forum Access
    Replies: 14
    Last Post: 07-07-2014, 07:12 AM
  5. stuck on the best way to do this
    By token_remedie in forum Queries
    Replies: 1
    Last Post: 09-22-2011, 10:19 PM

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