This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft relational database management systems designed for ease of use.
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.
There is more than one issue here. So, I am going to start with the first one. When you hit the Clear button it sounds like the Provider's Name is not requerying. I would add a Requery in the code of the Clear button. If that doesn't work then you need to make sure there is an Appointment Type for the Provider selected.
As for the sfrSearchSpecialty From you need to make sure the field you are using in the Filter code is in the Recordsource of the report.
I will check on the clear button in the morning, and possibly add a requery.
I tried to check the sfrSearchSpecialty form in the data tab to see if the record source field for filtering is the same as the report in the data tab record source field for filtering, but kept getting booted out before I could see the entire thing. It would not surprise me if that is the case.
The system is not letting me stay logged in from home, and keeps booting me out, so I will check on those two items as soon as I get to work in the morning. Thank you again for your time and help.
I hate when that happens! I'll be off and on tomorrow so hopefully it will be okay once you add the requery and check for the field.
Hi Gina,
You hit the nail on the head with both issues. Thank you so much!!! I'm working on adding the requery.
Meanwhile, the sfrSearchSpeciality and the rptResultsSpeciality both have different record source fields. How do I best determine which version of the record source is the best one to use? Or should I go back to the qrySearch and pull that record source out of there to use?
I also took a look at the Sub-Speciality version as the search there isn't quite right, it works but is a bit "off", and both the search and report also have different record source fields, so that was no help there to determine which version of the record source to use.
Just to make sure I'm looking in the right areas and we are on the same page, I've added some screen shots from the Specialty search and the Specialty report.
Thank you again for your time and help. Have a great day.