trying to create a scheduling doctor appointments in Access 2013

Anonymous

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.

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

Anonymous

2018-11-05T22:43:45+00:00

What happened to the one I sent you? That one worked.
Was this answer helpful?

0 comments No comments
Anonymous

2018-11-05T21:29:44+00:00

The forms open; and the reports all open.

The forms work sporadically in the search. Sometimes the search and reports come up correctly, other times the reports do not show any data. I am extremely frustrated. Sub-specialties still works great as I had that working correctly before the weekend. I've tried to copy whatever I did in sub-specialties into specialties three times this morning, to no avail.

Also the main search form is still not working right even though I removed Specialties and Sub-Specialties. Even if the search gives results (it comes up blank most of the time), it repeats the results:

I am also getting a box asking if I want to save changes, when I never made any changes, only when I run a search in the main search form:

My main search code:

SELECT tblProvider.pProviderID, [pLast] & " " & [pFirst] AS Provider FROM tblProvider ORDER BY tblProvider.pLast;

SELECT tblVisit.vVisitID, tblVisit.vType FROM tblOfficeProvider INNER JOIN tblVisit ON tblOfficeProvider.opOfficeProviderID = tblVisit.vOfficeProviderID WHERE (((tblOfficeProvider.opProviderID)=[Forms]![frmSearch]![cboProviderName])) ORDER BY tblVisit.vType;

SELECT tlkpInsurances.iInsuranceID, tlkpInsurances.iName FROM tlkpInsurances INNER JOIN tblProviderInsurance ON tlkpInsurances.iInsuranceID = tblProviderInsurance.piInsuranceID WHERE (((tblProviderInsurance.piProviderID)=[Forms]![frmSearch]![cboProviderName])) ORDER BY tlkpInsurances.iName;

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

Here is what is working great - Sub-specialties:

My Sub-Specialties code which is working correctly:

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

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

My Specialties code which is not working correctly:

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, tlkpSubSpecialty.ssSubSpecialtyID 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;

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

Thank you for your time and help.
Was this answer helpful?

0 comments No comments
Anonymous

2018-11-05T17:22:14+00:00

Make the Forms work on their own, don't worry about anything else. That way you can get the most important things workings! Good luck!
Was this answer helpful?

0 comments No comments
Anonymous

2018-11-05T16:23:09+00:00

Thank you again so much for your time and help. I got a reprieve until noon tomorrow as they really want this working. So I will work on trying to make the subforms available on their own and the subreports.
Was this answer helpful?

0 comments No comments
Anonymous

2018-11-05T00:07:38+00:00

hi,RO!

I am afraid you had better step by step.

1,building query without parameters.

please show the sample datas what you had got as source tables and the query result based on these tables.

if you got the correct answer then move to next step. if not, be free to discuss here.

2.add some parameters to querys you have set up.

3.the last step is to build form or report.

At first just to show information a little, just test table with few fields and add field one by one.

first thing first is to divide a big problem to several small questions,I think.
Was this answer helpful?

0 comments No comments