Physical Therapy Project

Question

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.

Answer 1

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;

Answer 2

I have the Primary and Special Interests working now. I'm going to start building the "home" page. I don't know if I need to do the subforms like in previous projects, or if there is a better way to build a "home" page. I'm going to do a bit of research, but it may be best to stick with what I know and have experience in (subforms).

I can't believe how easy this all is, now that I finally got the basics of MS Access. Thank you to all the contributors and forum moderators that have helped and supported me, especially when I was discouraged on previous projects.

Answer 3

I've made some changes, as I want to keep this project as simple as possible and hopefully get it done quickly.

I've added "Credentials" and "Status" to tlkpTherapist.

I've built a new table called tblClinicTherapistDay, using tblClinicTherapist and tlkpDay. I used all the day combinations in the table, such as M, W, F or Tu, Th or M - F or M - Th or Tu - F, etc.

Several therapists work in several clinic locations, but they work different days in each clinic location, so tblClinicTherapist was needed first, to assign therapists to their various clinic locations. tblClinicTherapistDay should drill down which clinic they are at on which days. I attach as much as I can to the tTherapistID, but had to attach days to the tblClinicTherapistID in a workaround for the Therapists working various locations and days of week.

The search queries so far are working great with all the tables, including credentials, status and days.

Now I will build the Primary and Special Interests tables, then start attaching TherapistIDs to those. Then will work on the query for that.

So far everything has been easy to do, as I've really learned a lot from everyone's assistance on my other projects. Thank you everyone, I really appreciate everything I have learned from you all.

Answer 4

Thank  you Tom. I appreciate your suggestions and help.

Answer 5

For concatenation, see http://www.theaccessweb.com/modules/mdl0004.htm

I can see from your diagram that you don't have enforced relations. That is a mistake. Right-click each line and check the box, so these relations actually have teeth.