Like* Criteria not Pulling all Data

[Lee Ratzlaff]

Hello,

I have created a tracking DB for a clinic. It has four tables, and one of my queries that pulls from two of them is not working properly.

I am trying to pull all actively enrolled clients by the name of their clinician.

The query SQL: SELECT [Youth Contact Info].[Youth Name], [Youth Contact Info].MRN, Enrollment.[Intake Date], Enrollment.[Assigned Clinician]
FROM [Youth Contact Info] LEFT JOIN Enrollment ON [Youth Contact Info].[Youth Name] = Enrollment.[Youth Name]
WHERE (((Enrollment.[Assigned Clinician]) Like [Enter Clinician Name] & "*") AND ((Enrollment.[Enrollment status])="assigned"))
ORDER BY Enrollment.[Assigned Clinician];

When I enter one of the clinician's names, their active clients appear. But, when I enter any of the other clinician names, the query is empty - even though they have active clients assigned.



Any thoughts as to why this query is not pulling the data for the other clinicians?
Thanks

[CJ_London]

without seeing your data, difficult to advise. Perhaps other clinicians don't have any records in the other table, or if they do, they do not have a status of assigned.

Not sure why you have a left join

[Lee Ratzlaff]

I used the query builder to create this, so the join exists between the two tables: Youth Contact Info and Enrollment, as their contact info is entered prior to enrolling them in the clinic.

The other clinicians do have assigned clients. If they did not, this would not be an issue.

[CJ_London]

I used the query builder to create this, so the join exists between the two tables:

but why a left join, the default would be an inner join so you must have decided to make it a left join for some reason? That is saying 'include all records from youth contact info whether or not there is a matching record in enrollment'. But you want to know what clients are assigned to a clinician. Your left join would typically be used to identify youths who do not have a matching clinician.

Suggest change to an inner join.

You have asked for thoughts which I have provided. Other thoughts include perhaps you have not spelt the clinician name or the youth name correctly - joins on names is not a good way to go, you should be using primary and foreign keys. As I said before difficult to advise without seeing some data.

The other clinicians do have assigned clients. If they did not, this would not be an issue.

suggest you prove this statement

[Lee Ratzlaff]

Ah, I understand now! I changed the join: SELECT [YouthContact Info].[Youth Name], [Youth Contact Info].MRN, Enrollment.[Research ID],Enrollment.[Assigned Clinician]
FROM [Youth Contact Info] INNER JOIN Enrollment ON [YouthContact Info].[Youth Name] = Enrollment.[Youth Name]
WHERE (((Enrollment.[Enrollmentstatus])="assigned") AND ((Enrollment.[Assigned Clinician]) Like[Enter Clinician Name] & "*"));

The table includes clients assigned to severalclinicians, and when I remove the Like [Enter Clinician Name] &"*" criteria, it shows all of the active clients by all clinicians.With the Like* criteria, it pulls those assigned to this one specific clinicianwhen entering her full name, but only one of the others. I tried entering thefirst two letters of another clinician’s name, and then her active clients didquery correctly. But, there are several other clinicians whose clients do notappear.

I am spelling the clinician names correctly.
Youth name is primary key in both tables.

At this point, I am considering removing the criteria,but then they will only see the full list of active clients (ascending byclinician) instead of being able to search for only their own caseload.

Thank you very much for your advice.

[CJ_London]

Youth name is primary key in both tables

bad idea. You should use an autonumber PK. Names change. Also by making it PK in both tables, you can only assign one clinician, you might as well have clinician in your youth table

When I enter one of the clinician's names, their active clients appear. But, when I enter any of the other clinician names, the query is empty - even though they have active clients assigned.

this tells me something is wrong with your data, either the structure or the values or both. I've already highlighted a structural issue. Suggest provide some example data for a clinician that does work, and one that doesn't. show it in the same form as your tables

[Lee Ratzlaff]

I can't share data, as it is protected by HIPAA

I changed the PK to an autonumber, but no change in query output

I have a different table for Clinician info, as there are several (and more temp ones join yearly) and I also need to keep track of their supervisor and other information about them. I have a join between the Enrollment table and the Clinician table to select the clinician name from the list.

When I run the query with the like* criteria, the query does not provide any results for the majority of names I enter who I know have assigned clients. If I enter the first two letters of one of the clinician's first name, her assigned clients appear in the query, but not if I enter her full first name.

It must be an issue with the names, but I entered them all the same way in the table initially. I just don't understand why the Like [Enter Clinician Name] & "*" criteria would not work with all names the same.

[orange]

Lee,

I recommend you make a copy of your evolving database. Then change clinician and other related, identifiable data values to some mockups --Porky Pig, Sheeza Payne, Paige Turner etc etc.
We are not interested in the data values per se. We are trying to help but need something tangible to work with.
Your struggle is with structure, not data values.
Good luck.

[CJ_London]

if you can't provide data then I don't think I can help any further. Your query looks OK - and works for one clinician so most likely to do with the data or your table design.

Suspect you are breaking the HIPAA rules by using names on your joining fields anyway and I don't think providing the table design falls within HIPAA so you should be able to show that - take a screenshot of the relevant tables in the relationships form.

Can you not create some dummy youths/clinicians to demonstrate the problem?

[Lee Ratzlaff]

I guess I don't understand what you want to see. The query is literally empty when I enter clinician names. Nothing to show.

I'm not violating HIPAA in any way with this DB - it's stored on a local server and is password protected.

[Lee Ratzlaff]

query screen shot attached

[CJ_London]

query looks OK so the problem is with your data - find a clinician that should return youths and then check the youth name in both tables - they need to be exactly the same, no spaces at the end, no hidden characters for example (often occurs when copy pasting form another source)

your enrollment table should have a field called something like 'YouthID' which would match to the record ID in the youth table - and that is what your join should be based on.

As a general observation, good practice is to not have spaces in field names and to give field names meaningful names - record ID? which table? call it YouthID, EnrollmentID, ClinicianID etc. Also naming conventions should avoid starting with a number.

And I see you are using multivalue fields which will cause you problems down the line and in some cases you are using incorrectly - how many different races can a person be?