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.
I will have to send it to you in the morning. I had to log in to the work server from home tonight to try to download it, and it kept booting me out so I will try again in the morning. I really appreciate your time and help very much.
I am taking tomorrow off but if you want to send to me I'll look at on Sunday. If you do please detail what you want because next week is kind of crowded and I don't want to leave you waiting for answers.
I ended up bringing in two subforms as the search still is not working. I was also afraid people may be confused with everything the way it used to be - with only one search button. The two sub-forms are called sfrSearchSpecialty and sfrSearchSubSpecialty to the right of the screen. So now I have:
If I do search on the subform Speciality for "Orthopedic Surgery" it comes up, and gives us all the Ortho Surgeons so that's great:
If I do a search on the subform Specialty for Internal Medicine, it gives me three IM providers in two different offices, so I am happy with that, as it is working somewhat. It's not picking up all IM providers in all offices though. The first office actually has five providers, and the second office has two providers.
If I do search on the subform SubSpeciality for "Knee" it comes up, and gives us all the Ortho Surgeons that work on knees, so that's great. Unfortunately it doesn't work on the other subspecialities and I'm not sure why.
Unfortunately the main search, with Provider + Visit + Insurance is returning a ton of duplicates, repeating the one item over and over.
I will be working on this all weekend, trying to fix the main search so it does not repeat 50 times, and also trying to fix the other searches. Thank you so much for your time and help.
Main Search:
Private Sub BtnSearch_Click()
On Error Resume Next
Dim strWhere As String
Dim strSQL As String
Dim lngLen As Long
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboProviderName) Then
strWhere = strWhere & "([pProviderID] = " & Me.cboProviderName & ") AND "
End If
If Not IsNull(Me.cboVisitType) Then
strWhere = strWhere & "([vVisitID] = " & Me.cboVisitType & ") AND "
End If
If Not IsNull(Me.cboInsurance) Then
strWhere = strWhere & "([piInsuranceID] = " & Me.cboInsurance & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
strSQL = strSQL
DoCmd.OpenReport "rptResults", acViewReport
DoCmd.Maximize
Else
strWhere = Left$(strWhere, lngLen)
strSQL = strSQL & " WHERE " & strWhere
DoCmd.OpenReport "rptResults", acViewReport, , strWhere
DoCmd.Maximize
Reports![rptResults].Filter = strWhere
Reports![rptResults].FilterOn = True
End If
End Sub
Private Sub cboProviderName_AfterUpdate()
' set Visit Type and Insurance combo boxes to Null
' and requery controls to show Visit Type in selected Provider
Me.cboVisitType = Null
Me.cboVisitType.Requery
Me.cboInsurance = Null
Me.cboInsurance.Requery
' requery form to show Provider choice
Me.Requery
End Sub
Private Sub cboVisitType_AfterUpdate()
' set Insurance combo box to Null
' and requery control to show Insurance in Visit Type
Me.cboInsurance = Null
Me.cboInsurance.Requery
' requery form to show Visit in selected Visit Type
Me.Requery
End Sub
Private Sub cboInsurance_AfterUpdate()
' requery form to show Insurance name in selected Insurance
Me.Requery
End Sub
Private Sub cboVisitType_Click()
Me.cboVisitType.Requery
End Sub
Private Sub cboInsurance_Click()
Me.cboInsurance.Requery
End Sub
Specialty Search:
Option Compare Database
Option Explicit
Private Sub BtnResetSpecialty_Click()
Dim ctl As Control
For Each ctl In Me.Section("Detail").Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
End Sub
Private Sub BtnSearchSpecialty_Click()
On Error Resume Next
Dim strWhere As String
Dim strSQL As String
Dim lngLen As Long
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboSpecialty) Then
strWhere = strWhere & "([sSpecialtyID] = " & Me.cboSpecialty & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
strSQL = strSQL
DoCmd.OpenReport "rptResultsSpecialty", acViewReport
DoCmd.Maximize
Else
strWhere = Left$(strWhere, lngLen)
strSQL = strSQL & " WHERE " & strWhere
DoCmd.OpenReport "rptResultsSpecialty", acViewReport, , strWhere
DoCmd.Maximize
Reports![rptResultsSpecialty].Filter = strWhere
Reports![rptResultsSpecialty].FilterOn = True
End If
End Sub
Private Sub cboSpecialty_Click()
Me.cboSpecialty.Requery
End Sub
Sub-Specialty Search:
Option Compare Database
Option Explicit
Private Sub BtnResetSubSpec_Click()
Dim ctl As Control
For Each ctl In Me.Section("Detail").Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
End Sub
Private Sub BtnSearchSubSpec_Click()
On Error Resume Next
Dim strWhere As String
Dim strSQL As String
Dim lngLen As Long
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboSubSpecialty) Then
strWhere = strWhere & "([ssSubSpecialtyID] = " & Me.cboSubSpecialty & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
strSQL = strSQL
DoCmd.OpenReport "rptResultsSubSpecialty", acViewReport
DoCmd.Maximize
Else
strWhere = Left$(strWhere, lngLen)
strSQL = strSQL & " WHERE " & strWhere
DoCmd.OpenReport "rptResultsSubSpecialty", acViewReport, , strWhere
DoCmd.Maximize
Reports![rptResultsSubSpecialty].Filter = strWhere
Reports![rptResultsSubSpecialty].FilterOn = True
End If
End Sub
Private Sub cboSubSpecialty_Click()
Me.cboSubSpecialty.Requery
End Sub
Hi Gina,
Thank you for your reply. I just saw this when I came on just now to post an update of my project. I will try this next. Meanwhile, I will post an update. The "drop dead" deadline is Monday morning at 8 a.m. so I have to fix the rest of the issues this weekend.
Thank you again for all your time and help. I really appreciate it very much.
If you are going to use a List Box then you are going to need a different set of lines. See...