trying to create a scheduling doctor appointments in Access 2013

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

You are off to a good start.  Let's not worry about the Forms or the queries until we get a wee bit more work on those tables.  Have a look at...

https://www.access-diva.com/dm20.html

I realize the above is not for a doctor's office but it is for appointments.  I will also add a few observations...

1. No need to put the word *Junction* in your Table Names.  You really want to keep those table names as short as possible... you'll thank me later.
2. Remove the underscores, awkward to type and later on you'll be kicking yourself for adding them.
3. Adapt a Naming Convention and stick to it.  Have a look at...

https://www.access-diva.com/d1.html

4. BIT field (Yes\No) can be handy but let's use those sparingly as they are a real pain if you ever think you might to upsize to SQL Server.

Don't do it!

You are talking about private personal information that is covered by federal and state/provincial laws.  You have to be able to secure this information so that it cannot be hacked. Large, experienced companies are constantly losing information. You do not want to be blamed for that sort of problem, and you certainly don't want to be on the wrong side of privacy lawsuits.

I have 20+ years of programming experience, but not with the specific features you mentioned, so I would not undertake this sort of project on my own.

If you insist ...

Do you have experience with Database systems (DB2, Oracle, MySQL, MS SQL, etc) other than Access so that you have a good general idea of what you are doing?

If you don't, again I suggest you don't do it.

Learning on the job is fine for small tasks, but you are building a system that is going to be used by many people and has to be used over the internet / network.  Not a simple thing to do, well. Your boss would be better off simply buying an "off the shelf" application and letting the vendor worry about troubleshooting bugs and enhancements.

If you are expecting to be doing more similar applications I'd suggest finding a simpler one to start with.

You already have one example system, I'd suggest googling for other free examples you can look at a and learn from.

If you still insist ...

Do you have a clear set of "requirements" from the users / owner?  By that I mean have they listed the features they expect to see in it. Here are just a few examples:

• multi user
• multi location
• what user devices will you be using: desktop, laptop, tablet, smartphone?
• how many transactions per day / per hour: new appointments, changes, deletions
• all uses must have up to date access to all doctors, all location schedules

or each location has stand alone schedule?

• who can schedule Dr's appointments?
• can doctors make appointments?
• can people in other locations schedule appointments for your locations/Drs?
• what sort of output do they want, ie daily schedule printed out, daily schedule on smart phone or tablet or desktop only?
• how do Dr's get notified about changes in their current schedule
• do you want to be able to auto generate email reminders to patients?
• do you want to be able to auto generate email notifications to patients when appointment has to be rescheduled, ie Dr called out to emergency
• Do you want to be able to auto generate email notifications to warn patients DR is running late. I have only once in my life had a Dr appointment that started on time. Most run slightly late, many are up to an hour or more late.
• does the application have to be compliant with local, state/provincial, federal "Accessibility" laws or regulations, ie http://accessibletech.org/access\_articles/software/softwareAppAccessible.php

You need to do this before starting because otherwise you will end up with a never ending series of change requests: "I want this extra information saved in it...",  "... I want to be able to do this thing I saw in XYZ application ... " ,  "... I don't like the way this screen looks ..." etc.  I worked on a similar, small, in house application once (for a large company).  We had clearly defined requirements, we worked closely (daily contact) with a couple of client area consultants.  When we developed it and released it (after about three months work) just one person in the initial test group had a "problem" with the "form filling" application that was explicitly specified by the users, and they ended up rejecting it.  Fortunately we were able to get funding to completely re-write it in another application language. We used the first one as a working prototype for the new & improved version.

If you still insist on doing this application, you should look in to using "Azure" as the backend that runs the database.  It has a lot of built in security that will help with that requirement.

How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database**http://www.devhut.net/2014/01/13/how-to-hybridize-your-ms-access-database-in-office-365-azure-database/#comment-1063******January 13, 2014  Daniel Pineault

Connect an Access Front End located on local computer to back end Access DB on Azure

*****************  Resources  ******************

Here are a bunch of links to various Access resources. Some are free, some are paid.

Change in software, including Office apps, tends to be "evolutionary" rather than "revolutionary". In short that means that change in programs tends to be slow, small changes. New features are added, very few features are removed.  So if you find a book or lesson for an earlier version most of the lessons will still be usable in newer versions, you just have to make allowances for changes in the user interface, ie switch from menu to ribbon appears to be "revolutionary", but is only evolutionary.  The ribbon was simply "bolted on" to the same underlying program that the menu accessed.  They put "new lipstick on the same old pig".

Book suggestions-**http://www.access-diva.com/booklist.html******

Access Database Best Practices: How to Deploy Your Access Database to Multiple Usershttp://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm

A common question raised by many Access users creating a multi-user database is how best to deliver your solution so that it is secure, fast, and upgradeable.  Access database deployment within your organization, or to your end customers, should be well thought out in advance to avoid performance issues, reliability problems, or potentially significant security or data loss issues.  This article discusses key best practices to deploy your Access database. Getting STarted

·     http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/

·     http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/

·     http://www.devhut.net/2012/12/04/ms-access-where-start-learning-database-tutorials/

·     http://www.devhut.net/2011/12/15/ms-access-splitting-and-deploying-your-database-part-2/

·     http://www.devhut.net/2016/09/01/securing-your-ms-access-database-front-end/

DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Access video traininghttps://support.office.com/en-us/article/access...

Learning Access 2016https://www.linkedin.com/learning/learning-acce...

Learning Access for Office 365https://www.lynda.com/Access-tutorials/Learn-Ac...

Learning Relational Databaseshttps://www.lynda.com/Access-tutorials/Learning...

·     Access 2013 Quick Start Guide

·     What’s new in Access 2013

·     Access training

·     Make the switch to Access 2013

·     Basic tasks for an Access app

      Basic tasks for an Access 2013 desktop database

**Work with databases in Access******http://office.microsoft.com/en-us/office365-suite-help/redir/HA104126140.aspx

Introduction to Access 2016 Kennesaw State U

Download free Introduction to Microsoft Access 2016, course tutorial, PDF file made by Kennesaw State University.

Access 2003 Keyboard Shortcuts

Best STL   <edit: Update LInks>

​

www.stl-training.co.uk/access-vba-training.php

www.stl-training.co.uk/download/manuals/Access-2007-Best-STL-shortcut-keys.pdf

www.stl-training.co.uk/access-advanced-courses.php

www.stl-training.co.uk/download/manuals/Access-2003-Best-STL-shortcut-keys.pdf

www.stl-training.co.uk/access-introduction-courses.php

www.stl-training.co.uk/access-intermediate-courses.php

ACCESS 2010: PART I  (Free) http://bookboon.com/en/access-2010-part-i-ebook 
Stephen Moffat, The Mouse Training Company
Premium Free PDF English Pages 109
Access 2010 is a powerful Database application that allows users to produce tables, forms, queries and reports within…

Access 2010: Part II Tables and Queries (Paid) https://bookboon.com/en/access-2010-part-ii-ebook
Stephen Moffat, The Mouse Training Company 

Access 2010: Part III Forms and Reports  (Paid)
https://bookboon.com/en/access-2010-part-iii-ebook
Stephen Moffat, The Mouse Training Company

Access 2010: Part IV (Paid) http://bookboon.com/en/access-2010-part-iv-ebook
Stephen Moffat, The Mouse Training Company
English Pages 106
Access 2010 is a powerful database application that allows users to produce tables, forms, queries and reports within…

Access 2007: Part I (Paid)
https://bookboon.com/en/access-2007-part-i-ebook
Stephen Moffat, The Mouse Training Company

ACCESS 2007: PART II  (Paid) http://bookboon.com/en/access-2007-part-ii-ebook
Stephen Moffat, The Mouse Training Company
Premium PDF English Pages 126
Part 2 provides detailed explanations of useful topics and tools including: saving data in Access, working…

ACCESS 2007: PART III (Paid) http://bookboon.com/en/access-2007-part-iii-ebook
Stephen Moffat, The Mouse Training Company
Premium PDF English Pages 98
Part 3 provides detailed explanations of useful topics and tools including: creating forms, form controls…

ACCESS 2003  http://bookboon.com/en/access-2003-ebook
Stephen Moffat, The Mouse Training Company
Premium PDF English Pages 148
Access 2003 starts with an explanation of the visual layout of the Access screen and provides an overview to planning…

Mouse Training Company https://web.archive.org/web/20131127145031/http://www.mousetraining.co.uk/ms-office-training-manuals.html
Free Office training manuals download

MS Access 2007 Training Manuals
MS Access 2007 Training Manual

These books are paid, but the site is a source of good material.  If you do decide to buy any of these sign up for their newsletter first so you can get a discount on the books

Access 2013: the real startup guide
$9.95–$17.95 Select options

Access Archon: Working with EXCEL https://office-watch.com/product/access-archon-working-with-excel/

Access 2016 Bible **** Kusleika and I updated our Access 2016 Bible.
I don’t know if it ever got to #1 in Amazon’s Database category (like \*\*\*\* was pushing for), but I just checked and it #6.

Access 2013 Keyboard Shortcuts    PDF

How to Learn Microsoft Access: 5 Free Online Resources -
https://www.makeuseof.com/tag/learn-microsoft-access-5-free-online-resources/  

Microsoft’s Access Training -  https://support.office.com/en-in/article/Access-2013-training-courses-videos-and-tutorials-a4bd10ea-d5f4-40c5-8b37-d254561f8bce **** Seeing as how Access is a Microsoft product, who better to teach you the ins-and-outs of how this program is meant to be used? Fortunately, Microsoft provides a series of online videos that cover several different topics for Beginners and Intermediates.
Each video is between 15 to 20 minutes long. The first one covers the key concepts and principles that make Access what it is, and subsequent videos explore skills like database creation, querying for data, and using advanced query parameters.

**GCF Learn Free** -  http://www.gcflearnfree.org/access2013
GCF Learn Free is an initiative by the Goodwill Community Foundation that aims to teach skills necessary for prosperity in the 21st Century. The site is home to 1,000+ lessons across various subjects, and they’re all available for free.
Of those lessons, 19 of them cover basic Access education. Most are available in either text format or video format (both formats include helpful diagrams and illustrations to guide you along). The ones without videos are short enough that video wouldn’t add much value.
While these aren’t the most in-depth tutorials, they’re great as an introduction and as refresher lessons in case you get rusty and need a few reminders.

**Quackit Access Tutorial** ****   -
 http://www.quackit.com/microsoft\_access/tutorial/
Though Quackit is technically a resource for web developers, it does contain a short and sweet tutorial series for Access. Like GCF Learn Free’s Access series, this one is good for an introductory overview. As such, it should be supplemented with a more in-depth resource.
If you’re interested in learning more about web development, consider checking out Quackit’s other tutorials on databases and SQL (including MySQL and Microsoft SQL Server).

**Holowczak’s Access Tutorial**.  - 
http://holowczak.com/microsoft-access-2007-and-2010-tutorial/
Rich Holowczak is a computer guru who has worked with numerous computer systems leading all the way back to the Apple II and has been teaching computer science for several decades. Suffice it to say that this guy knows his stuff.
His website is home to a number of tutorials, but his biggest hit is his Microsoft Access series. It comes in two versions: the original was written for Access 97/2000/2003 while the second version is updated for Access 2007/2010/2013. We recommend the second one.
The series starts with basic concepts and interface tips, moves onto manipulation of tables/queries/forms, and ends with a handful of more complicated topics like reports, switchboards, and advanced database design.

PC Learning Zone YouTube Series

 -
https://www.youtube.com/watch?v=ySyMn3H4rvg&list=PLhKFRV3-UgpeA_3wzRHF8AS8T7ppKvm9O

It doesn’t take much effort to find plenty of YouTube playlists for learning how to code, which is a good thing since YouTube instructionals can be quite helpful — and that’s the case with PC Learning Zone‘s multiple series.
The Access Beginner Level 1 series is comprised of 13 videos that last a little over 3 hours in total. There’s another playlist called Access Level 2 which digs a bit deeper. As of now, the Level 2 series is halfway done and still in production.
If you look through the channel’s playlists, you’ll find other helpful series for Word, Excel, and older versions of Access. Want to brush up on your Office skills? This is a great resource for doing that.
Hint: Here are some tips for setting up YouTube for better learning.

No problem.  We'll be here!

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.