Re: [MS_AccessPros] removing duplicates in a query

 

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

__._,_.___

---

Posted by: pattykf@cox.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___