Patty-
Your problem is your join is like this:
dbo_wo_tobacco_detailswithcess_ <— [Current Smokers Encounters] —> dbo_wo_plan_detail
When you get a "hit" in both the leftmost and the rightmost table, you get the Cartesian product of the rows that match Current Smokers Encounters - one row each equals two rows.
And why are you using RIGHT and LEFT joins again? I would think you would want only the rows that match in both tables.
One way to solve this would be to use one query that joins detailswithcess and current smokers and select those that have education (education?) completed. Then UNION that with a second query that does a join with current smokers and plan detail, selecting those who have had cessation education.
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
(Paris, France)
On Feb 9, 2015, at 9:14 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have a query where there are two fields that could have tobacco interventions in it, so I want to see each encounter that has a 1 in the dbo_wo_plan_detail_.tobacco_cessation_education field or has a 1 in the dbo_wo_tobacco_detailswithcess_.ecucation_completed field. Person nbr is being pulled into the query since some of the patients are no longer active patients but for this query if they had an encounter in the year I needed them. (it is in the [current smokers Encounters] query where the second pers number field is)
my query is pulling the data in so that It has two lines of data for the same encounter number
SELECT dbo_wo_plan_detail_.person_id, dbo_wo_plan_detail_.enc_id, dbo_wo_plan_detail_.tobacco_cessation_education, [Current Smokers Encounters].tobacco_yes, [Current Smokers Encounters].LastOfvisit_type_expanded, [Current Smokers Encounters].[Encounter Last in period].person_nbr, [Current Smokers Encounters].last_name, [Current Smokers Encounters].first_name, [Current Smokers Encounters].date_of_birth, [Current Smokers Encounters].dbo_person.person_nbr, dbo_wo_tobacco_detailswithcess_.ecucation_completed
FROM dbo_wo_tobacco_detailswithcess_ RIGHT JOIN ([Current Smokers Encounters] LEFT JOIN dbo_wo_plan_detail_ ON [Current Smokers Encounters].person_id = dbo_wo_plan_detail_.person_id) ON dbo_wo_tobacco_detailswithcess_.enc_id = dbo_wo_plan_detail_.enc_id
WHERE (((dbo_wo_plan_detail_.tobacco_cessation_education)>0)) OR (((dbo_wo_tobacco_detailswithcess_.ecucation_completed)>0));
thank you for your help
Patty
my query is pulling the data in so that It has two lines of data for the same encounter number
SELECT dbo_wo_plan_detail_.person_id, dbo_wo_plan_detail_.enc_id, dbo_wo_plan_detail_.tobacco_cessation_education, [Current Smokers Encounters].tobacco_yes, [Current Smokers Encounters].LastOfvisit_type_expanded, [Current Smokers Encounters].[Encounter Last in period].person_nbr, [Current Smokers Encounters].last_name, [Current Smokers Encounters].first_name, [Current Smokers Encounters].date_of_birth, [Current Smokers Encounters].dbo_person.person_nbr, dbo_wo_tobacco_detailswithcess_.ecucation_completed
FROM dbo_wo_tobacco_detailswithcess_ RIGHT JOIN ([Current Smokers Encounters] LEFT JOIN dbo_wo_plan_detail_ ON [Current Smokers Encounters].person_id = dbo_wo_plan_detail_.person_id) ON dbo_wo_tobacco_detailswithcess_.enc_id = dbo_wo_plan_detail_.enc_id
WHERE (((dbo_wo_plan_detail_.tobacco_cessation_education)>0)) OR (((dbo_wo_tobacco_detailswithcess_.ecucation_completed)>0));
thank you for your help
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) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar