ACCESS FEED: [MS_AccessPros] Re: complex criteria problem

Rabu, 28 Oktober 2015

[MS_AccessPros] Re: complex criteria problem

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.

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 27, 2015, at 8:18 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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