Left join not working

[wmdmurphy]

I have the following query in my app, and hoped to see 10 rows for each
project in the query results since I'm doing a left join on
tbl_TitlesToIncludeInReports and since this table has 10 title records.

The summary query qry_BudgetsForSelectedBudgetYear contains anywhere from 2
to 10 records within each project that match rows in
tbl_TitlesToIncludeInReports.

The query results only include the rows from
qry_BudgetsForSelectedBudgetYear that match rows in the table, as if it was
an equal join.

SELECT qry_BudgetsForSelectedBudgetYear.BudgetYear,
qry_BudgetsForSelectedBudgetYear.ProjectName,
tbl_TitlesToIncludeInReports.TitleName,
qry_BudgetsForSelectedBudgetYear.SumOfAllocation AS Allocation,
tbl_TitlesToIncludeInReports.SortOrderInReports
FROM tbl_TitlesToIncludeInReports LEFT JOIN qry_BudgetsForSelectedBudgetYear
ON tbl_TitlesToIncludeInReports.TitleName =
qry_BudgetsForSelectedBudgetYear.JobTitle
ORDER BY qry_BudgetsForSelectedBudgetYear.ProjectName,
tbl_TitlesToIncludeInReports.SortOrderInReports;

Any help will be appreciated.

Bill

 

[Stefan Hoffmann]

hi Bill,

Any help will be appreciated.

First of all: Use table alias names and format your query before
posting... otherwise they are really hard to read:

SELECT
B.BudgetYear,
B.ProjectName,
T.TitleName,
B.SumOfAllocation AS Allocation,
T.SortOrderInReports
FROM tbl_TitlesToIncludeInReports T
LEFT JOIN qry_BudgetsForSelectedBudgetYear B
ON T.TitleName = B.JobTitle
ORDER BY B.ProjectName, T.SortOrderInReports;

The query results only include the rows from qry_BudgetsForSelectedBudgetYear that match rows in the table, as if it was an equal join.

This is due to the use of B.ProjectName as order predicate. Try running
the query without the ORDER BY clause...


mfG
--> stefan <--

 

[wmdmurphy]

Stefan,

Thanks, I'll use alias names in the future.

I removed the order by clause, but I'm still getting the same results. It's
as if the query was an equal join instead of a left join.

Bill

 

[John Spencer MVP]

You probably need to use a cartesian query to generate all ther reprots titles
and then use that in your next query.

Something like the following two query solution.

SELECT Distinct TitleName, JobTitle, SortOrderInReports
FROM tbl_TitlesToIncludeInReports, qry_BudgetsForSelectedBudgetYear

Save that as Q (or whatever name you want)

SELECT qry_BudgetsForSelectedBudgetYear.BudgetYear,
qry_BudgetsForSelectedBudgetYear.ProjectName,
Q.TitleName,
qry_BudgetsForSelectedBudgetYear.SumOfAllocation AS Allocation,
Q.SortOrderInReports
FROM Q LEFT JOIN qry_BudgetsForSelectedBudgetYear
ON Q.TitleName = qry_BudgetsForSelectedBudgetYear.JobTitle
ORDER BY qry_BudgetsForSelectedBudgetYear.ProjectName,
Q.SortOrderInReports;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Stefan Hoffmann]

hi Bill,

I removed the order by clause, but I'm still getting the same results. It's
as if the query was an equal join instead of a left join.

Hmm, take a look at your data again. Is the direction of your left join
correct or must it be changed (FROM qry LEFT JOIN tbl)?


mfG
--> stefan <--

 

[wmdmurphy]

John,

I gave this a try, but it produced a lot of rows that don't seem right. For
example, here are the first 10 rows of the query you named Q below:

qry_BudgetLeftJoin1stStep TitleName JobTitle SortOrderInReports
Controller Controller 30
Controller On-site non-exempt 30
Controller Project Accountant 30
Controller Regional Accounting Trainer 30
Controller Senior Controller 30
Controller Senior Project Accountant 30
Controller Staff Accountant I 30
Controller Staff Accountant II 30
Controller Staff Accountant III 30
Controller Vice President-Controller 30


When I combined this query into the second query below I got 1,470 rows,
which is 10 times more than expected. The table
tbl_TitlesToIncludeInReports has 10 rows, and
qry_BudgetsForSelectedBudgetYear has 147 rows, so it's the product of these
two.

I would expect to get back something between 147 and 200 rows since there
are some rows in tbl_TitlesToIncludeInReports that don't exist in
qry_BudgetsForSelectedBudgetYear .

Bill