Physical Therapy Project

Anonymous
2019-02-05T00:08:30+00:00

I am now starting a physical therapy project.

Here are my relationships so far:

So far the queries I have built are working good and giving me nice results.

My first question is basically about concatenating data.

Example 1:  I have 35 therapists. All therapists have at least one degree. About 16 therapists out of the 35 therapists have two or three degrees. What is the best way to concatenate their degrees?  By using the concatenate & in a string? My problem is that there are various amounts of degrees, so I'm not finding it easy to concatenate their degrees unlike their names - for example, FullName: [tFirst] & " " & [tLast] Or is there another way to handle this? I don't plan on creating a search by degree, but that may occur in the future.

Example 2:  The therapists and their Status. Some therapists are Per Diem (on call), some therapists are part time, and so on. Some therapists have more than one status (a few are part time and per diem). What's the best way to handle this so their status appears on one line? Would waiting until building the reports be best before handling this, and putting it in reports? I will be having a search by status, especially for the Per Diems therapists.

Example 3:  Some therapists work 1 day a week; some work 2 days; some 3; some 4; some 5; and some work 6 days a week. I have the days entered, but I won't get to building a table with therapists and their days until later. I'm assuming I will have to figure out a way similar to the above two examples to have the days show up nicely. Or should that also wait until I do reports? There will be a search for days by Therapy Type (ie, Physical Therapy, Occupational Therapy, Speech Therapy and Wound Therapy).

I hope my question is making sense. Thank you for your time and help.

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
Answer accepted by question author
Anonymous
2019-02-26T18:15:54+00:00

Hmm, just realized you need your WHERE statement to include both Visit and Clinic...

SELECT tblClinicTherapist.ctTherapistID, tlkpClinic.cName, [tFirst] & " " & [tLast] AS FullName

FROM (tlkpTherapist INNER JOIN (tlkpClinic INNER JOIN tblClinicTherapist ON tlkpClinic.cClinicID = tblClinicTherapist.ctClinicID) ON tlkpTherapist.tTherapistID = tblClinicTherapist.ctTherapistID) INNER JOIN tblClinicTherapistVisit ON tblClinicTherapist.ctClinicTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID

WHERE (((tblClinicTherapistVisit.ctvVisitID)=[Forms]![frmSearch]![cboVisit]) AND ((tlkpClinic.cClinicID)=[Forms]![frmSearch]![cboClinic]))

ORDER BY tlkpTherapist.tLast;

Was this answer helpful?

2 people found this answer helpful.
0 comments

59 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2019-02-22T23:23:15+00:00

    Wow, thank you so much Gina. I really appreciate your time and help.

    The second search now finally works except for the report coming up blank but I think that's an easy fix for me.

    As for the first search, it is working a lot better. The Visit and Clinic cbo boxes are working great. But when I go to select Therapist it is pulling up all therapists in that specific clinic, and not narrowing down the specific therapists that do that particular appointment. There are three therapists that do aquatic therapy but it is pulling up all of the therapists. Same with Dry Needling, there are three therapists that do that. Speech Therapy, there are only three therapists. Cancer, there should be only two therapists, Pelvic Floor, there should be only one therapist.

    I honestly don't know what I'd do without your help Gina. I've learned a lot from you, and thank you so much.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-02-22T22:43:09+00:00

    So the cboTherapist at the top you need to change the Row Source to...

    SELECT tlkpTherapist.tTherapistID, tlkpTherapist.tLast FROM tlkpTherapist INNER JOIN tblClinicTherapist ON tlkpTherapist.tTherapistID = tblClinicTherapist.ctTherapistID WHERE (((tblClinicTherapist.ctClinicID)=[Forms]![frmSearch]![cboClinic])) ORDER BY tlkpTherapist.tLast; 

    As for the second one where the Search button won't work.  Well not sure what you are looking and no matter what it doesn't have TherapistID or FullName in the first column of the the combo box, see below.  You have Clinic Name...

    SELECT tlkpClinic.cName, [tFirst] & " " & [tLast] AS FullName, tlkpVisit.vName FROM (tlkpTherapist INNER JOIN (tlkpClinic INNER JOIN tblClinicTherapist ON tlkpClinic.cClinicID = tblClinicTherapist.ctClinicID) ON tlkpTherapist.tTherapistID = tblClinicTherapist.ctTherapistID) INNER JOIN (tlkpVisit INNER JOIN tblClinicTherapistVisit ON tlkpVisit.vVisitID = tblClinicTherapistVisit.ctvVisitID) ON tblClinicTherapist.ctClinicTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID WHERE (((tblClinicTherapistVisit.ctvVisitID)=[Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    So, to fix that one, change the cboTherapist Row Source to...

    SELECT tlkpVisit.vVisitID, tlkpClinic.cName, [tFirst] & " " & [tLast] AS FullName, tlkpVisit.vName FROM (tlkpTherapist INNER JOIN (tlkpClinic INNER JOIN tblClinicTherapist ON tlkpClinic.cClinicID = tblClinicTherapist.ctClinicID) ON tlkpTherapist.tTherapistID = tblClinicTherapist.ctTherapistID) INNER JOIN (tlkpVisit INNER JOIN tblClinicTherapistVisit ON tlkpVisit.vVisitID = tblClinicTherapistVisit.ctvVisitID) ON tblClinicTherapist.ctClinicTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID WHERE (((tlkpVisit.vVisitID)=[Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast; 

    Then adjust the lines behind the Search button to...

        If Not IsNull(Me.cboTherapist) Then

            strWhere = strWhere & "([vVisitID] = " & Me.cboTherapist & ") AND "

        End If

    And change these Properties in the Property Sheet for cboTherapist...

    Column Count: 3

    Column Widths: 0";0";2"

    That should do it.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-02-22T22:24:02+00:00

    Watching my machine work (running a script) so taking a look.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-02-22T21:30:23+00:00

    No kids, and I'm profoundly deaf since birth, so I wear hearing aids that amplify everything. I don't have the ability to block out or tune out noise, so it's a constant loud roaring noise all day long.

    I'm not having any luck with the latest version. It's very difficult to concentrate in here especially when people have "Friday Fever" and are louder than usual in this warehouse/boiler room setting with over 60 people.

    The Visit > Therapist issue from yesterday is still not working.  Management also wants Visit > Clinic > Therapist. I've taken both as far as I can, tried so many different things and nothing works. I've gone into older files to see if I could figure out anything but had no luck as I never did set up the Visit > Therapist search button as I was trying to get the Therapist name working correctly first.

    This is the latest version. I write all SQL and VBA by hand then type it in, so I'm not sure if there's a way to get the program to automatically do the SQL or VBA for you or something.

    https://drive.google.com/file/d/1u0G5dExY93a0TRb7USjhtN_eVyzRYIHT/view?usp=sharing

    Thank you so much for your time and help.

    Was this answer helpful?

    0 comments