Access SQL Left Join Not Working Correctly

[Kansas11]

Hi, I'm trying to write a SQL query within MS Access (2007) that results in a listing of just the records in the left table (Actuals) that are not in the right (Budget).
I currently have the following query:

SELECT
qry_Actual_TeamMember.Activity_Description,
qry_Actual_TeamMember.Team_Member,
qry_Actual_TeamMember.Company,
SUM(qry_Actual_TeamMember.Today_ETC) AS Today_ETC,
0 AS Bgt_Year_2010,
SUM(qry_Actual_TeamMember.Act_Year_2010) AS Act_Year_2010,
SUM(0-[qry_Actual_TeamMember]![Act_Year_2010]) AS Year_Hrs_Remain,
SUM((0-[qry_Actual_TeamMember]![Act_Year_2010])- qry_Actual_TeamMember.Today_ETC) AS Year_Remain_Less_ETC

FROM qry_Actual_TeamMember

LEFT JOIN qry_Budget_TeamMember
ON (qry_Actual_TeamMember.Team_Member = qry_Budget_TeamMember.Team_Member)
AND (qry_Actual_TeamMember.Activity_Code = qry_Budget_TeamMember.Activity_Description)

WHERE qry_Budget_TeamMember.Team_Member IS NULL

GROUP BY qry_Actual_TeamMember.Activity_Description, qry_Actual_TeamMember.Company, qry_Actual_TeamMember.Team_Member

ORDER BY qry_Actual_TeamMember.Activity_Description, qry_Actual_TeamMember.Company, qry_Actual_TeamMember.Team_Member;

The result has all the records from from the left table (qry_Actual_TeamMember) and assigns all a 0 value for Bgt_Year_2010. But in my source data, there is only one record in the left table that is not in the right. From what I understand of the processing sequence of the WHERE clause in LEFT JOINs, it my query should only output records that are not in the right table.


If it helps, my data looks like this:

qry_Actual_TeamMember:
Activity_Description Team_Member Company Today_ETC Act_Year_2010
Paint Joe A 2 20
Paint Sam B 2 10

qry_Budget_TeamMember:
Activity_Description Team_Member Company Bgt_Year_2010
Paint Joe A 30

So, I want the query above to just list the record for Sam with a Bgt_Year_2010 = 0, but right now it lists Joe and Sam with Bgt_Year_2010 = 0 even through Joe's Bgt_Year_2010 = 30 (Joe's correct info is already coming through in an INNER JOIN that I am UNION'ing with this query)

Can any one help me get this query where I want it to be?

I wish access would just allow a full outer join, it would make this task a whole lot easier!

 

[Joe4]

Welcome to the Board!

Look at tge second line of your Left Join

AND (qry_Actual_TeamMember.Activity_Code = qry_Budget_TeamMember.Activity_Description)

According to your sample data layout below, I do not see any field called "Activity_Code" in qry_Actual_TeamMember.

 

[Kansas11]

Welcome to the Board!

Look at tge second line of your Left Join

AND (qry_Actual_TeamMember.Activity_Code = qry_Budget_TeamMember.Activity_Description)

According to your sample data layout below, I do not see any field called "Activity_Code" in qry_Actual_TeamMember.

Sorry, i just posted a snippet of sample data. The Activity_Description in the Budget table is a lookup from an Activity table so the value stored is the primary Key (the activity_code) but the value shown it is description, so I have it named description for user friendliness. I had a guy in a SQL server post tell me the code looked fine for SQL but thought maybe Access processes it differently.

 

[Joe4]

Access typically will process it the same was as SQL does, as long as you have posted valid code. I am trying to re-create the scenario, but cannot, as I am getting errors due to to the discrepancies I mentioned in the Activity_Code/Activity_Description fields. It causes errors due to the missing fields.

If you can post all the necessary parts of the "real" code, along with all the necessary sample data, I can try to re-create it tonight and see if I can discern what is happening.

 

[Kansas11]

Hi,
I actually figured it out last night. I nested my LEFT JOIN select statement within another select that had the where clause:
SELECT *
FROM (SELECT.... LEFT JOIN.....)
WHERE Team_Member IS NULL

Thanks for the help though