ACCESS FEED: Re: [MS_AccessPros] complex criteria problem

Selasa, 27 Oktober 2015

Re: [MS_AccessPros] complex criteria problem

SELECT DISTINCT PersonID WHERE PersonID
IN (SELECT PersonID from table2 WHERE blah blah)
OR PersonID
IN (SELECT PersonID from table3 WHERE blah blah)

warm regards,
crystal

Document Query SQL, Form and Report RecordSources
http://www.rogersaccesslibrary.com/forum/document-query-sql-form-and-report-recordsources_topic606.html
Create Word documents with SQL for queries, form and report RecordSources, and make a query showing database objects

~ have an awesome day ~

On 10/27/2015 1:18 PM, pattykf@cox.net [MS_Access_Professionals] wrote:

I am trying to pull all patients that may have had a final diagnosis on the visit in one of 7 possible diagnosis of hypertension or HTN (it could be any combo of those two labels). What I tried to do is pull it for each field, and I got the patient listed a number of times for each encounter, so if the patient had 5 different diagnosis and one was the one identified, it came back in 5 rows. I am sure there is something minor that I am doing wrong, but this brain is not processing it correctly to get it to only show up each encounter that has the word on one row

the first 5 assessments are in one table the 2others are on a different table, and I used Person to link the tables together to identify the patient.

this is what I have written:

SELECT dbo_master_im_.person_id, dbo_master_im_.enc_id, dbo_master_im_.create_timestamp, dbo_master_im_.assessment1, dbo_master_im_.assessment2, dbo_master_im_.assessment3, dbo_master_im_.assessment4, dbo_master_im_.assessment5, dbo_IMAssessment_.Assessment6, dbo_IMAssessment_.Assessment7
FROM (dbo_person LEFT JOIN dbo_master_im_ ON dbo_person.person_id = dbo_master_im_.person_id) LEFT JOIN dbo_IMAssessment_ ON dbo_person.person_id = dbo_IMAssessment_.person_id
WHERE (((dbo_master_im_.assessment1)="hypertension" Or (dbo_master_im_.assessment1) Like "HTN*")) OR (((dbo_master_im_.assessment2)="hypertension" Or (dbo_master_im_.assessment2) Like "HTN*")) OR (((dbo_master_im_.assessment3)="hypertension" Or (dbo_master_im_.assessment3) Like "HTN*")) OR (((dbo_master_im_.assessment4)="hypertension" Or (dbo_master_im_.assessment4) Like "HTN*")) OR (((dbo_master_im_.assessment5)="hypertension" Or (dbo_master_im_.assessment5) Like "HTN*")) OR (((dbo_IMAssessment_.Assessment6)="hypertension" Or (dbo_IMAssessment_.Assessment6) Like "HTN*")) OR (((dbo_IMAssessment_.Assessment7)="hypertension" Or (dbo_IMAssessment_.Assessment7) Like "HTN*"));

any help will be much appreciated

thank you so much

I would also like eventually to have this show the patient once but count the number of visits that one of those two diagnosis show up.

APPRECIATE support

Have a great day/evening

Patty