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-10-30T02:25:04+00:00

Hmm, well I am kind of tied up right now but will look at tomorrow.

Things for you to do. Open the report right from the Navigation Pane and if you get pop-ups or error fix those first. Try the button again and see if it works now without the pop-ups.

I will review the filters tomorrow because I believe you forgot text deliminator's for the last two but can't be sure until I look at more closely.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-30T00:19:59+00:00

Thanks, Al. I was afraid of that.

;o)
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-30T00:03:39+00:00

My EMR goes back to the DOS days (Paradox for DOS) which I eventually ported over to MS Access 95 in 1995. It would have been really nice to follow an organized naming convention, but alas I didn't and as the EMR grew, so did the hodgepodge of randomly named forms.

Now it's not worth the effort, as I'd have to change the underlying code too. I sort of manage my home office the same way and everything is all over the place until my wife explodes and picks things up. Then we don't speak to each other for 4-5 days...

If I have to do a do-over of a form, like this scheduler form, I simply do it here for the Access forums and update things, one module at a time, and hopefully I can help someone along the way. Otherwise, I pretty much update 1-2 issues daily, about 5 days a week, sometimes between patients.
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-29T22:48:22+00:00

I've added the fourth combo box and the fifth text box to the code. I am feeling very overwhelmed and in over my head right now. Maybe a good night's sleep will help and a fresh look in the morning.

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.txtSubSpecialty) Then

        strWhere = strWhere & "([pSubSpecialty] = " & Me.txtSubSpecialty & ") AND "

    End If

    lngLen = Len(strWhere) - 5

        If lngLen <= 0 Then

            strSQL = strSQL

            DoCmd.OpenReport "rptResults", acViewPreview

            DoCmd.Maximize

        Else

            strWhere = Left$(strWhere, lngLen)

            strSQL = strSQL & " WHERE " & strWhere

            DoCmd.OpenReport "rptResults", acViewPreview, , strWhere

            DoCmd.Maximize

            Reports![rptResults].Filter = strWhere

            Reports![rptResults].FilterOn = True

        End If

End Sub
Was this answer helpful?

0 comments No comments
Anonymous

2018-10-29T22:24:13+00:00

This is the original record source:

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, tblProvider.pSubSpecialty, tblProvider.pNotes, tblVisit.vType, tblVisit.vArrival, tblVisit.vNotes, tblVisit.vLength, tlkpInsurances.iName, tlkpSpecialty.sSpecialty, tblProvider.pProviderID, tblVisit.vVisitID, tblProviderInsurance.piInsuranceID

FROM 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;

This is the updated record source:

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, tblProvider.pSubSpecialty, tblProvider.pNotes, tblVisit.vType, tblVisit.vArrival, tblVisit.vNotes, tblVisit.vLength, tlkpInsurances.iName, tlkpSpecialty.sSpecialty, tblProvider.pProviderID, tblVisit.vVisitID, tblProviderInsurance.piInsuranceID, tlkpInsStatus.isStatus

FROM 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;

So it looks like the code was updated. Or am I misunderstanding you?
Was this answer helpful?

0 comments No comments