ACCESS FEED: Re: [MS_AccessPros] removing duplicates in a query

I just realized on this one I need to limit the patients that have had a diagnosis greater than 6 months, so I need to find a way to use the dx date the field is dbo_problem_list_date.create.timestamp. I am not sure this there is a way to do at TODAY feature in Access, I have done it in excel but never tried in access

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

this is the same query except I changed the dx and the date of birth ranges
I would like the diagnosis description to show so that I can check my work with code limitations (dx code is 401.1 thru 401.9) so I am thinking the 401.* covers that but I do have diagnosis queries to write that have diagnosis ranges that are not so simple and will need to see the diagnosises to make sure that I have captured patients correctly. when I add the diagnosis description field it messes up the results so I know I am adding it incorrectly. this is the field and table: dbo_problem_list_date.problemdescription

this is the hypertension sql query

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id) INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE (((dbo_person.date_of_birth)>"19300101" And (dbo_person.date_of_birth)<"19970101") AND ((dbo_person.person_id) In (SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*401.*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)))));

also for my learning, where in the code does it limit the return of patients to once only? I know it is working to only return them once but I can't figure out where.
thank you
Patty

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

that worked
the dbo_person is used to be able to sort on the Date of birth since I could not get the Perosn_filter_new date of birth that was converted to filter date of birth ranges. the person filter new, limits some people and the encounter last period was a way to eliminate some other features of inactive patients.
But the long and short of it is that this worked to limit the patients with 250 code to only show them once. I did not need the diagnosis code info to show, had it showing to check work and determine what was happening. but it is not needed once the query works

thank you so much for this, off to work on other diagnosis queries.

Patty

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

Patty-

It's not clear what the purpose of the INNER JOIN with dbo_person (I assume a table) and Person_filter_New (I assume a query) is supposed to do.

From your original query, it looked like you were using dbo_problem_list_data to find people with diabetic diagnosis. The subquery is supposed to return the person_id of all people where DiagnosisCode Like "*250.0*" and there is no date resolved. Your outer query should simply be returning the people in that list - further filtered for age.

Yes, I eliminated [Encounter Last In Period] - perhaps in error. If this is further filtering the people, then it needs to go back in, like this:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id)

INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101")
AND dbo_person.person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

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 Feb 9, 2015, at 5:35 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

it returned more people than less people, something is weird here. when I looked at the design of the query it had no dx table or encounter limiting table anymore. I added back in dx and the dx was not limited to the 250.0* code. I am going to see if I can look at the code of the two and see what changed that I could adapt. I am thinking it is something little that is just missing.
Patty

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

Patty-

Ah, there's a person_id in both dbo_pers and Person_filter_new. This should fix it:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101")
AND dbo_person.person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

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 Feb 9, 2015, at 4:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

this is resulting in an error:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101")
AND person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

I can't find a fix for it.
tried
thank you

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

Patty-

If you need to display the diagnosis detail info in your query, then you will get one record per diagnosis, not one record per person. If all you need is the person info, then do this:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101"))
AND person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

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 Feb 9, 2015, at 3:48 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query that shows me all of my active patients with diabetes between the age of 18 and 75 yrs of age based on diagnosis. Unfortunately some of patients have two diabetic diagnosis in the table that are slightly different. I would like the query to result only unduplicated patients.

Here is the current SQL query:
SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_problem_list_data_.ProblemDescription, dbo_problem_list_data_.DiagnosisCode, dbo_problem_list_data_.create_timestamp, dbo_problem_list_data_.Resolved, dbo_person.date_of_birth, dbo_problem_list_data_.person_id
FROM ((dbo_problem_list_data_ INNER JOIN Person_filter_New ON dbo_problem_list_data_.person_id = Person_filter_New.person_id) INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id) INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0) AND ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101"))
ORDER BY dbo_problem_list_data_.create_timestamp DESC;

thank you the field that has unique numbers is
Person_filter_New.person_nbr

Patty