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.

trying to create a scheduling doctor appointments in Access 2013

Anonymous

Would this be correct? I'm still not getting it to work, and would think OR would work???

Option Compare Database

Option Explicit

Private Sub BtnReset_Click()

Me.cboProviderName = ""

Me.cboVisitType = ""

Me.cboInsurance = ""

Me.cboSpecialty = ""

Me.cboSubSpecialty = ""

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

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 & ") OR"

End If

If Not IsNull(Me.cboSpecialty) Then

strWhere = strWhere & "([sSpecialtyID] = " & Me.cboSpecialty & ") OR "

End If

If Not IsNull(Me.cboSubSpecialty) Then

strWhere = strWhere & "([ssSubSpecialtyID] = " & Me.cboSubSpecialty & ") OR"

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

0 comments

Anonymous

I broke the database again: :( I changed the SubSpecialty text box to a table as I couldn't get the text to work, and created two more tables. The search needs to search by "Provider Name, Visit Type, Insurance" OR Specialty (no other Combo Boxes); OR Sub-Specialty (no other Combo Boxes).

So my relationships now are:

Then I updated the macro:

Option Compare Database

Option Explicit

Private Sub BtnReset_Click()

Me.cboProviderName = ""

Me.cboVisitType = ""

Me.cboInsurance = ""

Me.cboSpecialty = ""

Me.cboSubSpecialty = ""

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

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

If Not IsNull(Me.cboSpecialty) Then

strWhere = strWhere & "([sSpecialtyID] = " & Me.cboSpecialty & ") AND "

End If

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 "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

My Record Source in Report is:

SELECT tblOffice.oName, tblOffice.oUrgent, tblOffice.oNotes, tblOffice.oRefill, tblOffice.oBack, tblOffice.oFront, tblOffice.oEmail, tblOffice.oAddress, tblOffice.oCity, tblOffice.oState, tblOffice.oZip, [tblProvider].[pFirst] & " " & [pLast] AS Expr1, tblProvider.pSpecialty, tlkpSubSpecialty.ssName, tblProvider.pNotes, tblVisit.vType, tblVisit.vArrival, tblVisit.vNotes, tblVisit.vLength, tlkpInsurances.iName, tlkpSpecialty.sSpecialty, tblProvider.pProviderID, tblVisit.vVisitID, tblProviderInsurance.piInsuranceID, tlkpInsStatus.isStatus, tblProviderSubSpecialty.pssSubspecialtyID, tlkpSubSpecialty.ssName FROM tlkpSubSpecialty INNER JOIN ((tlkpInsStatus INNER JOIN (tlkpSpecialty INNER JOIN ((tblProvider INNER JOIN ((tblOffice INNER JOIN tblOfficeProvider ON tblOffice.[oOfficeID] = tblOfficeProvider.[opOfficeID]) INNER JOIN tblVisit ON tblOfficeProvider.[opOfficeProviderID] = tblVisit.[vOfficeProviderID]) ON tblProvider.[pProviderID] = tblOfficeProvider.[opProviderID]) INNER JOIN (tlkpInsurances INNER JOIN tblProviderInsurance ON tlkpInsurances.[iInsuranceID] = tblProviderInsurance.[piInsuranceID]) ON tblProvider.[pProviderID] = tblProviderInsurance.[piProviderID]) ON tlkpSpecialty.sSpecialtyID = tblProvider.pSpecialty) ON tlkpInsStatus.isStatusID = tblProviderInsurance.piInsStatusID) INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID;

So it would be nice to just search Specialty alone:

Or by Sub Specialty alone:

Thank you so much for your time and help.

0 comments

Anonymous

Great! You're most welcome.

0 comments

Anonymous

Oh my gosh. That worked! You've been such a huge help. Thank you so much!!! I'm really learning a lot from you.

0 comments

Anonymous

Look at the Groupings in the bottom it says *expression* that is because those Group Headings no longer exist in your Recordset but they are on your Report. You are not adding them you are fixing where it says *expression*.

0 comments

trying to create a scheduling doctor appointments in Access 2013

136 answers