trying to create a scheduling doctor appointments in Access 2013

Anonymous
2018-10-17T22:25:24+00:00

My boss asked me to do a new Access database from scratch. I’ve never done an Access database on my own before, and would appreciate any advice you guys have. This is to help employees schedule appointments for 46 medical doctors in 14 different medical offices.

As for my relationships, I hope I’ve set up everything correctly:

As you can see, I’ve set up two junction tables, but am not quite sure how to set up a search query with junction tables. I’ve looked in my two Access 2013 books, and I’ve also looked online but am not having much luck. For example, with the table Junction_Provider_Insurance, I’m not sure how to search the Junction table to pull up the doctor and if the doctor is contracted, not contracted, needs authorization or pending for the insurance. So if a doctor’s name and an insurance name such as Aetna is searched, I want the result to show that the doctor is or is not contracted with Aetna in ReportResults.

As for the search, I originally set up a wildcard search for all the search boxes. My original intention was that the user could type in anything they wanted in one or two or three of the boxes (such as doctor’s name, visit type and insurance) and do a search where the results come up. However this is not working at all and time is of the essence as I’ve already missed my deadline to turn in this a week ago.  

Due to missing the deadline, I decided to change the wildcard text search to a combo box for the Doctor’s Name, Medical Office Name, Visit Type, Specialty and Insurance in the hopes the search will finally work. I’m thinking the best way to handle this is to remove the medical office, and then just have the doctor’s name, then when that comes up, to cascade into a new combo box for the visit type (new patient, follow up, pre-op, post-op, etc.) Or is there a better way to do this? I still need the results to show in the report too.

Thank you so much for your time and help. I really appreciate it very much.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

136 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2018-10-19T20:40:15+00:00

    Hi Gina,

    Thank you for your time and help.

    I have changed to a Naming Convention. I've rebuilt the tables as best as I could. I added a few new lookup tables such as InsStatus (Insurance Status) in order to remove the BIT fields from the Insurances.

    Was this answer helpful?

    0 comments
  2. Tom van Stiphout 40,216 Reputation points MVP Volunteer Moderator
    2018-10-19T02:00:01+00:00

    Hi Al,

    Can you please send me a private message? tom 7744 at cox dot net

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-10-18T23:13:46+00:00

    No problem.  We'll be here!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-10-18T22:56:14+00:00

    Thank you so much Al and Gina. I have started making the changes you both suggest and will post an update once I complete the changes. I really appreciate your suggestions and help.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2018-10-18T00:56:37+00:00

    Well, doing an EMR for a physician's office can be daunting, but the trick is to start off with a basic set of tables, forms, and reports. MS Access is an excellent relational database to do this. I've used this platform for 28 years as an oncologist and since 2009 in my Internal Medicine only office. My EMR has about 450 forms and another 450 reports. It ties together Access, Excel (for billing, sending electronic bills to Office Ally), Word, and Outlook. I love it, and still add to it about 3-4 times a week. Just start with small steps at a time...

    The most important thing is 1) to make sure that you can bill (smart to match your providers with what insurance companies that they use) and 2) make sure you can schedule patients. For the latter you need a scheduler, so make sure that you have a decent scheduling form. Eventually you can branch off into a full EMR to follow your patients electronically. In prior versions of Access there used to be a calendar.ocx control that was perfect for this but that was taken out after 2010. :( Fortunately, there are many calendar controls that you can use out there to take it's place, and some of the best ones are free. In fact, I did one back in January that has the same look and feel of the original ocx control. Instead of making a calendar form, plopping in the ocx control into it, you need to start with a calendar form and program the calendar features around the embedded control. Here is how my scheduler looks like:

    This is how this scheduler started:

    URL download:  https://app.box.com/s/z199jbpxqjcexsprthqqe1o0r8rui4e4

    Among your tables, you are missing a PATIENT data table (s.a. for address, phone numbers, insurance information, general notation field), a table for their MEDICATIONS, and a table for GENERAL MEDICAL INFORMATION etc.

    Going back to connecting patient with provider insurances, you can have a clickbutton on your scheduler that can popup a simple form that shows if a match occurs:

    That's a lot to swallow... hope this helps.

    Al

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments