ACCESS FEED: Re: [MS_AccessPros] Re: complex criteria problem

Kamis, 29 Oktober 2015

Re: [MS_AccessPros] Re: complex criteria problem

Patty-

Crystal's suggestion is a good one. You could do it all in one query using complex criteria in the two WHERE clauses in the subqueries.

SELECT dbo_master_im_.person_id

FROM dbo_person

WHERE person_id IN

(SELECT person_id FROM dbo_master_im_

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 person_id IN

(SELECT person_id FROM dbo_IMAssessment_

WHERE (((dbo_IMAssessment_.Assessment6)="hypertension" Or (dbo_IMAssessment_.Assessment6) Like "HTN*")) OR (((dbo_IMAssessment_.Assessment7)="hypertension" Or (dbo_IMAssessment_.Assessment7) Like "HTN*")));

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 29, 2015, at 12:13 AM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I would love to change the tables but unfortunately I did not create the tables, this is the cruel world of electronic health records and people making them that have no idea what is really happening in the clinical world. I get the great pleasure of figuring out a way to make something decent out of a horrible situation. Thankfully they do not even allow an 8th assessment. Trailing underscores and all, I get to find ways to work their crappy designs. pull out meaningful data in their bad design with rows of data for every encounter a patient may have in the system

would utilizing the code idea given by crystal:

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

give me single rows of data or would it pull the same patient in multiple rows for each encounter?

I am thinking that maybe I would be even better to write one query for each assessment field, and then reduce that to the last time a patient had that diagnosis in that assessment field, do that for all the assessment field, then merge that data into one query that returns the patients that show up on any of the query's for each assessment

I am trying my best to limit writing a bunch of queries but if that really is the best way I will do what gives the right result

Unfortunately I will ultimately need to do this for a number of different diagnosis

THANK YOU for assistance, wishing the world of electronic health records was better but it is what it is and we are stuck finding ways to make this work in population medicine measures

Have a great evening

Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

Well, for starters, your table design is atrocious. assessment1, 2, 3, 4, 5, 6, 7 is a repeating group. There should be one row per assessment, not one column. What happens when there is an 8th assessment?

Also, when you create a JOIN like this:

dbo_master_im_ <— dbo_person —> dbo_IMAssessment_

… you'll get the cartesian product of all rows in the first and third tables for each person. If there are three records in the first table and five records in the second table for a given person, you'll get 15 records in the output.

And never mind that your table names with trailing underscore characters are just silly.