ACCESS FEED: Re: [MS_AccessPros] complex criteria problem

Selasa, 27 Oktober 2015

Re: [MS_AccessPros] complex criteria problem

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@cox.net [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

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•