473,224 Members | 1,464 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Access 2003: Left Join won't work - need help please

Hello,

I have what seems like a simple left join query,
but Access returns results like it's an inner join. I've tried
numerous combinations without success.

I have a table (ProjectList) with one row per project.
Each project has a status condition of Green, Yellow
or Red. The other table (DisplayOrder) has three rows,
one for each status condition color.

My query needs to return three rows: Green, Yellow and
Red plus the number of projects for each color condition.
One row for each color, even if no projects have
that color status.

Each time I run the query, it only returns *two* rows,
Green and Yellow, but never Red (currently, there are no
Red projects).

Why does the left join not show a red row from the
DisplayOrder table?

Thanks in advance for any and all suggestions,
David

Here's the query:

SELECT DisplayOrder.Condition,
Count(*) AS Total_Projects
FROM DisplayOrder
LEFT JOIN ProjectList
ON DisplayOrder.Condition = ProjectList.Condition
WHERE ProjectList.Status = 'Active'
AND ProjectList.Program = 'AML'
GROUP BY DisplayOrder.Condition, DisplayOrder.DisplayOrder
ORDER BY DisplayOrder.DisplayOrder

Dec 18 '06 #1
2 4029
"David F" <Sa***********@yahoo.comwrote in
news:11**********************@n67g2000cwd.googlegr oups.com:
Hello,

I have what seems like a simple left join query,
but Access returns results like it's an inner join. I've
tried
numerous combinations without success.

I have a table (ProjectList) with one row per project.
Each project has a status condition of Green, Yellow
or Red. The other table (DisplayOrder) has three rows,
one for each status condition color.

My query needs to return three rows: Green, Yellow and
Red plus the number of projects for each color condition.
One row for each color, even if no projects have
that color status.

Each time I run the query, it only returns *two* rows,
Green and Yellow, but never Red (currently, there are no
Red projects).

Why does the left join not show a red row from the
DisplayOrder table?

Thanks in advance for any and all suggestions,
David

Here's the query:

SELECT DisplayOrder.Condition,
Count(*) AS Total_Projects
FROM DisplayOrder
LEFT JOIN ProjectList
ON DisplayOrder.Condition = ProjectList.Condition
WHERE ProjectList.Status = 'Active'
AND ProjectList.Program = 'AML'
GROUP BY DisplayOrder.Condition, DisplayOrder.DisplayOrder
ORDER BY DisplayOrder.DisplayOrder
Queries apply the where clause after building the resultset from
the joins.

Solutions:
1) move the WHERE to a different query that just filters the
projectlist and left join that query with Displayorder.
2) Change the where clause so that you get null responses.
WHERE (ProjectList.Status = 'Active'
OR ProjectList.Status is Null)
AND (ProjectList.Program = 'AML'
OR ProjectList.Program is Null)

Note the use of parentheses () to get the right nesting of
conditions.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 19 '06 #2

Bob Quintal wrote:
Queries apply the where clause after building the resultset from
the joins.

Solutions:
1) move the WHERE to a different query that just filters the
projectlist and left join that query with Displayorder.
2) Change the where clause so that you get null responses.
WHERE (ProjectList.Status = 'Active'
OR ProjectList.Status is Null)
AND (ProjectList.Program = 'AML'
OR ProjectList.Program is Null)

Note the use of parentheses () to get the right nesting of
conditions.
Bob,

That makes sense now; I wasn't aware about the order of the WHERE
processing taking place after the join. I used your option #1 and
query now works. Thanks!!

David

Dec 19 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
12
by: Phil Powell | last post by:
<cfquery name="getAll" datasource="#request.dsn#"> SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn, U.choiceId, U.experience, T.label AS teamLabel, R.label AS roleLabel FROM User U...
3
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,...
2
by: StaZ | last post by:
Hello, I would simply like to know if there's a way to disable the "feature" of MS Access that rewrites your queries "correctly"... This feature makes this : ...WHERE SomeBooleanField...;...
2
by: Alan Lane | last post by:
Hello world: I have 2 tables. Table 1 has Store Information (tblStoreInfo) and contains 123 records. Primary key is STORE4_ID. Table 2 has Gross Addition Information (tblDTTD_GA), by date and...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.