Re: [MS_AccessPros] Help needed, duplicate rows data

 

Patty-

For starters, you're not really getting a LEFT JOIN between NCM_Main and ncm_intervention because you have applied a filter to the "right" table.  To get the correct answer, you would have to embed a query on ncm_intervention and do the date filter inside the query.

I assume Person_Filter_New is a query and that it provides a filtered list of person_id.  You should embed a query on dbo_person and do the filter there.

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 Jun 22, 2015, at 11:48 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query that is pulling the Nurse Care Manager interventions. I thought I was all set but noticed that the Modified date is updating a number of times during her data entry. what I would like is the query to return the one entry per type (ncm Intervention) per date (intervention date).  (there are a number of types not sure if the query would need to pull the individual types first then reduce the duplicates or it could be done with all the interventions)

I am just not sure how to do that.

this is what the ugly query looks like a the moment:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, dbo_person.date_of_birth, dbo_ncm_intervention_dm_.intervention_type, dbo_ncm_intervention_dm_.intervention_date, dbo_ncm_intervention_dm_.modify_timestamp, Person_filter_New.insurance1, Person_filter_New.insurance2, Person_filter_New.PCP_DBP, Person_filter_New.person_id, dbo_ncm_intervention_dm_.create_timestamp, dbo_ncm_intervention_dm_.action_1, dbo_ncm_intervention_dm_.action_2, dbo_ncm_intervention_dm_.action_4, dbo_wo_NCM_main_.active_date, dbo_wo_NCM_main_.inactive_date, dbo_wo_NCM_main_.NCM_Name, dbo_wo_NCM_main_.complex_pt, dbo_wo_NCM_main_.NHP_High_Cost, dbo_wo_NCM_main_.ins_complex_type, dbo_wo_NCM_main_.current_care
FROM ((dbo_wo_NCM_main_ LEFT JOIN dbo_ncm_intervention_dm_ ON dbo_wo_NCM_main_.person_id = dbo_ncm_intervention_dm_.person_id) LEFT JOIN Person_filter_New ON dbo_wo_NCM_main_.person_id = Person_filter_New.person_id) LEFT JOIN dbo_person ON dbo_wo_NCM_main_.person_id = dbo_person.person_id
WHERE (((dbo_ncm_intervention_dm_.modify_timestamp)>#10/1/2014# And (dbo_ncm_intervention_dm_.modify_timestamp)<#4/1/2015#));

As always I am grateful for your assistance,

John I am sure some of this will look familiar to you, I keep trying to work the data better.

thank you

Patty

__._,_.___

---

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

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___