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

Senin, 09 Februari 2015

Re: [MS_AccessPros] removing duplicates in a query

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:

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