473,324 Members | 2,214 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,324 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 4035
"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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.