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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2019-02-21T00:37:10+00:00

    Hi,

    I made some changes to simplify things for the end users as I felt the original version was too confusing.

    I tried to do a cbo Search here:

    I was able to get my other cbo searches working without any issues quickly, but I am super stuck here and have spent 1.5 days on it. I would really appreciate any help. The issue seems to be my sfrVisitTherapist.

    The Appointment SQL is good. The Therapist Row Source SQL is not working, because there are therapists that move around to several clinics, so I have to use that one (ClinicTherapist) instead of just plain old Therapist.

    The current SQL in Therapist Row Source is:

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

    ORDER BY tlkpClinic.cClinicID, tlkpTherapist.tLast;

    I tried other SQL such as:

    SELECT tlkpTherapist.tTherapistID, tlkpTherapist.tLast FROM tlkpTherapist INNER JOIN tblClinicTherapistVisit ON tlkpTherapist.tTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID WHERE (((tblClinicTherapistVisit.ctvVisitID) = [Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    SELECT tlkpClinic.cClinicID, tlkpClinic.cName, tblClinicTherapistDay.ctdClinicTherapistID, [tFirst] & " " & [tLast] AS FullName FROM tlkpTherapist INNER JOIN (tlkpClinic INNER JOIN (tblClinicTherapist INNER JOIN (tlkpDay INNER JOIN tblClinicTherapistDay ON tlkpDay.dDayID = tblClinicTherapistDay.ctdDayID) ON tblClinicTherapist.ctClinicTherapistID = tblClinicTherapistDay.ctdClinicTherapistID) ON tlkpClinic.cClinicID = tblClinicTherapist.ctClinicID) ON tlkpTherapist.tTherapistID = tblClinicTherapist.ctTherapistID ORDER BY tlkpClinic.cClinicID, tlkpTherapist.tLast;

    SELECT tblClinicTherapistVisit.ctvClinicTherapistID, tlkpTherapist.tLast FROM tlkpTherapist INNER JOIN tblClinicTherapistVisit ON tlkpTherapist.tTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID WHERE (((tblClinicTherapistVisit.ctvVisitID) = [Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    SELECT tblClinicTherapist.ctTherapistID, [tFirst] & " " & [tLast] AS FullName FROM tlkpTherapist INNER JOIN tblClinicTherapistVisit ON tblClinicTherapist.ctTherapistID = tblClinicTherapistVisit.ctvClinicTherapistID WHERE (((tblClinicTherapistVisit.ctvVisitID) = [Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    I have uploaded the file here:

    https://drive.google.com/file/d/11aB2iUdoFm4jybgwPvQ6UNh8CvzGVL47/view?usp=sharing

    Thank you in advance for any time and help.

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2019-02-21T17:11:27+00:00

    I have spent about 20 hours on this SQL statement for Visit > Therapist:

    SELECT tlkpVisit.vVisitID, tlkpVisit.vName FROM tlkpVisit ORDER BY tlkpVisit.vName;

    SELECT 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) ON tlkpVisit.vVisitID = tblClinicTherapistVisit.ctvVisitID WHERE (((tblClinicTherapistVisit.ctvVisitID) = [Forms]![frmSearch]![sfrVisitTherapist].[Form]![cboVisit])) ORDER BY tlkpTherapist.tLast;

    I would really appreciate any insight / corrections on where I have gone wrong. The problem is that I have several therapists that work at various clinics on different days of the week. So I have the therapists attached to the clinics, which gives them an unique ID which is tblClinicTherapist.ctTherapistID, then the visits get attached to each ctTherapistID, then making it tblClinicTherapistVisit.ctvClinicTherapistID or ctvClinicTherapistID. The file link is above if anyone needs to download it. Thank you so much.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-02-21T18:43:58+00:00

    Well the Column Count and Column Widths is wrong.  It's for 4 columns with the first 3 set to 0 and you have 3 column in your query.  Fix that and you should be good.

    Was this answer helpful?

    0 comments