Share via

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?

1 person found this answer helpful.
0 comments

59 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-02-13T00:29:34+00:00

    Thank you so much Gina. I really appreciate your taking the time to look at this.

    I have moved tblTherapistPrimary and tblTherapistSpecial back to linking to Therapist. The days are when they are assigned to be at work all day at that particular clinic. Visits are the actual therapy (Ankle, Elbow, Knee, etc.).

    I am doing something similar to my previous projects -- enabling the end user to find therapists by name, or interests, or therapy type (physical therapy, speech therapy, occupational therapy, etc.) or by clinic.

    So far everything is done except for having trouble with two SQLs. It's weird as I had no trouble with a similar SQL (Visit Type then Clinic), but I can't seem to figure out the two below.

    Visit then Day:

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

    SELECT tlkpDay.dDayID, tlkpDay.dDay FROM tlkpDay INNER JOIN tblVisitDay ON tlkpDay.dDayID = tblVisitDay.vdDayID WHERE (((tblVisitDay.vdVisitID) = [Forms]![frmSearch]![cboVisit])) ORDER BY tlkpDay.dDay;

    Special Interest then Clinic:

    SELECT [tlkpSpecialInterest].[siSpecialInterestID], [tlkpSpecialInterest].[siName] FROM tlkpSpecialInterest ORDER BY [siName];

    SELECT tlkpClinic.cClinicID, tlkpClinic.cName FROM tlkpClinic INNER JOIN tblSpecialClinic ON tlkpClinic.cClinicID = tblSpecialClinic.scClinicID WHERE (((tblSpecialClinic.scSpecialID) = [Forms]![frmSearch]![cboSpecial])) ORDER BY tlkpClinic.cClinicID;

    The file is at:

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-02-11T04:43:13+00:00

    ->It would also help to know *what* you are doing.  I can't tell by your initial post if you creating appointments for the Therapists or tracking when Therapists are available?  Or maybe just making it so Clinics can print out lists.

    I am also interested in who will use access application and how to use it in ideal condition.

    patients or calling center receiver?print out or just search on sceen?

    what's the problem in current situation?

    what kind of problem can this access application settle at last?

    In another words,what's the goal of this project?

    As Gina said,it would be helpful to know the environment in detail -:)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-02-11T02:47:42+00:00

    Well, here's my problem, initial table set up...

    tblTherapistPrimary should be linked to Therapist.  It will follow them no matter what Clinic they are at and will be easier to filter with Clinic Specialties.

    If the Therapists Specialty is linked to their Primary then that is also where they should be linked in your Relationships.

    Not sure I understand the whole days for the Therapist.  Is that days they are available to be at the Clinic or days they are assigned to be there?  And why is Visit there?  Aren't those the same thing?

    It would also help to know *what* you are doing.  I can't tell by your initial post if you creating appointments for the Therapists or tracking when Therapists are available?  Or maybe just making it so Clinics can print out lists.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-02-08T19:23:16+00:00

    I'm stuck again.

    I have 36 therapists (and even more therapists and information coming). I'm not sure how to get all the primary interests and special interests together. I understand the very basics of concatenation, but I'm not sure concatenation will work in this case. For example, my experience with concatenation is that everyone has a first name and a last name, so everything is consistent: FullName: [nFirst] & " " & [nLast], but how do you concatenate something that has varying amounts?

    Every therapist has a varying number of Primary Interests and Special Interests. Some even have no Primary, and some have no Special Interests.

    For example:

    My actual Therapist / Primary Interest(s) table:

    My actual Therapist / Special Interest(s) table:

    My Relationships:

    Should I "fix" this and do something like this, using long text fields for tPrimary and tSpecial:

    I know it's not ideal programming, but I don't know what else to do and it's an easy fix that I used above when asking about the various degrees, various status, and various days worked.

    Thank you for your time, help and suggestions. I really appreciate it very much.

    Was this answer helpful?

    0 comments