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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-02-25T22:41:36+00:00

    You need to look at your recordsource. Take that row source and put it in a query, remove the criteria and look at your data.  If you see both those therapist in both clinics then you need to figure that out first.

    Then you are using INNER joins which means the data has to be available across ALL tables that are joined to show.

    Sorry that I can't look but this week is going to be one of those 80 hour weeks and I barely had time to type this answer.  Not complaining, just stating a fact.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-02-25T19:35:36+00:00

    Hi Gina,

    I'm still trying to figure out the row source SQL on the last item:

    I used:

    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])) ORDER BY tlkpTherapist.tLast;

    And it isn't quite there yet. It's still grabbing therapists at different clinics even though the specific clinic is selected.

    For example, if I select Cancer/Oncology, then either Clinic A or Clinic B, both therapists pop up. There is one therapist in Clinic A and one therapist in Clinic B, so if I select Clinic A, one therapist should only pop up, or if I select Clinic B, one therapist should only pop up.

    I also tried:

    SELECT tblClinicTherapistVisit.ctvClinicTherapistVisitID, tlkpClinic.cName, [tFirst] & " " & [tLast] FROM (tlkpTherapist INNER JOIN  (tlkpClinic INNER JOIN tblClinicTherapistVisit ON tlkpTherapist.tTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID)) WHERE (((tblClinicTherapistVisit.ctvVisitID)=[Forms]![frmSearch]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    and

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

    Thank you again for all your time and help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-02-23T02:22:12+00:00

    Good idea, food always helps!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-02-23T02:01:49+00:00

    Thank you so much Gina. I really appreciate your time and help. I will work on the Therapist row source at the top one after dinner, as a break might do me some good, rather than staring at this all week long like I have been.

    Was this answer helpful?

    0 comments