trying to create a scheduling doctor appointments in Access 2013

Anonymous
2018-10-17T22:25:24+00:00

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.

Microsoft 365 and Office | Access | For home | Windows

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

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-11-26T18:48:22+00:00

    I've tried your suggestions and now the SubSpecialities are repeating and the report is still not working. As discussed before, I got the SubSpecialties working alone, nested that into Specialties, got Specialties working then nested that into Providers, and so on and on. I'm getting really frustrated as this is the last thing I need to get working before I can complete the project. Everything else is working except for the Specialties and SubSpecialties searches to the right side of the "home page".

    I've uploaded it to here:

    https://drive.google.com/open?id=1KjMElWEHseiCHJCc142821F0yicY9lNe

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-11-26T16:58:43+00:00

    I'm not sure which one of those is attached to the Main Report but only the third report has both the fields it's asking for within the Recordsouce.  The Filter will only work on the Main Report, so you first need to ensure all the fields you are filtering on are, in fact, in the Recordsource of the Main Report.

    1. Here's the SQL for the record source for SubSpecialty:

    SELECT tblProvider.[pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tlkpSubSpecialty.ssName, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSubSpecialty INNER JOIN (tblProvider INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID

    ORDER BY tblProvider.pLast, tlkpSubSpecialty.ssName;

    1. Here's the SQL for the record source for Specialty:

    SELECT tblProvider.[pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tlkpSubSpecialty.ssName, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSubSpecialty INNER JOIN (tblProvider INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID

    ORDER BY tblProvider.pLast, tlkpSubSpecialty.ssName;

    1. Here's the SQL for the record source for Provider/Office:

    SELECT tblOffice.oName AS [Office Name], [pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tblOffice.oAddress, tblOffice.oCity, tblOffice.oState, tblOffice.oZip, tblOffice.oUrgent, tblOffice.oNotes, tblOffice.oBack, tblOffice.oFront, tblOffice.oEmail, tblOffice.oRefill, tlkpSpecialty.sSpecialtyID, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSpecialty, tlkpSubSpecialty, tblProvider INNER JOIN (tblOffice INNER JOIN tblOfficeProvider ON tblOffice.oOfficeID = tblOfficeProvider.opOfficeID) ON tblProvider.pProviderID = tblOfficeProvider.opProviderID

    ORDER BY tblOffice.oName;

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-11-26T16:33:50+00:00

    Because it is a "stacked report", with SubSpeciality inside Specialty, Specialty inside Provider and so on:

    Here's the SQL for the record source for SubSpecialty:

    SELECT tblProvider.[pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tlkpSubSpecialty.ssName, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSubSpecialty INNER JOIN (tblProvider INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID

    ORDER BY tblProvider.pLast, tlkpSubSpecialty.ssName;

    Here's the SQL for the record source for Specialty:

    SELECT tblProvider.[pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tlkpSubSpecialty.ssName, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSubSpecialty INNER JOIN (tblProvider INNER JOIN tblProviderSubSpecialty ON tblProvider.pProviderID = tblProviderSubSpecialty.pssProviderID) ON tlkpSubSpecialty.ssSubSpecialtyID = tblProviderSubSpecialty.pssSubspecialtyID

    ORDER BY tblProvider.pLast, tlkpSubSpecialty.ssName;

    Here's the SQL for the record source for Provider/Office:

    SELECT tblOffice.oName AS [Office Name], [pFirst] & " " & [pLast] AS Provider, tblProvider.pLast, tblOffice.oAddress, tblOffice.oCity, tblOffice.oState, tblOffice.oZip, tblOffice.oUrgent, tblOffice.oNotes, tblOffice.oBack, tblOffice.oFront, tblOffice.oEmail, tblOffice.oRefill, tlkpSpecialty.sSpecialtyID, tlkpSubSpecialty.ssSubSpecialtyID

    FROM tlkpSpecialty, tlkpSubSpecialty, tblProvider INNER JOIN (tblOffice INNER JOIN tblOfficeProvider ON tblOffice.oOfficeID = tblOfficeProvider.opOfficeID) ON tblProvider.pProviderID = tblOfficeProvider.opProviderID

    ORDER BY tblOffice.oName;

    Thank you for your time and help.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-11-26T04:45:16+00:00

    Please post the Recordsource of the Report in SQL.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2018-11-25T23:35:22+00:00

    Thank you Gina.

    I already have them in RecordSource and the report crashes. I've also added them to the original query and that crashes too.

    Was this answer helpful?

    0 comments