Obtaining the correct results from a two table query

[jo]

I have two tables say A and B, what I am trying to achieve with the query is
just to display records that have the received field ticked in table A and
the colour field in table B not containing “Y”.

How do I modify the code below to achieve this?

SELECT A_Date, A.Received, A.ID, B. Colour
FROM A LEFT JOIN B
ON DDP_A.Main = B.Main
WHERE (((A.Received)= -1) AND ((B.Colour <> “Y”));

 

[jo]

The code was just to demonstrate what I had in mind.

I have updated the example regarding bad table and field names. Could you
help with the unbalanced Parenthesis?

SELECT A.Date, A.Received, A.ID, B. Colour
FROM A inner JOIN B
ON A.Main = B.Main
WHERE (((A.Received)= -1) AND ((B.Colour <> "Y"));

 

[KARL DEWEY]

For every open you gotta have a close. Try this --
SELECT A.Date, A.Received, A.ID, B. Colour
FROM A inner JOIN B ON A.Main = B.Main
WHERE (A.Received= -1) AND (B.Colour <> "Y");

 

[Marshall Barton]

The code was just to demonstrate what I had in mind.

I have updated the example regarding bad table and field names. Could you
help with the unbalanced Parenthesis?

SELECT A.Date, A.Received, A.ID, B. Colour
FROM A inner JOIN B
ON A.Main = B.Main
WHERE (((A.Received)= -1) AND ((B.Colour <> "Y"));

Access's over enthusastic use of parenthesis might have
confused you. There should have been a ) just before the
semicolon. But, in this case, none of them are needed so
you can just remove all of them.

 

[jo]

Hi
Thank you for your reply's.

I tried the suggested update to my query but unfortunately it did not
display the correct result.

Hence to simplify fault finding the problem. I created two tables, a query
and a form / subform based on the example code we have been discussing.
Using test data so that Record 2 of table A would be flagged as it did not
have a Y in the colour field but did have a tick in Received field resulted
in no record returned by the query.
(No record returned at all, of any type)


I have supplied the following information as I must be overlooking
something.

Copy and pasted

SELECT A.Date_R, A.Received, A.ID, B.Colour
FROM A INNER JOIN B ON A.Main = B.Main
WHERE A.Received=-1 AND B.Colour <>"Y";



Table A has the following fields and data types

Date_R >>> Date/Time
Received >>> Yes/No
ID >>> Text
Main>>> AutoNumber>>> PK Field

Record 1 has the following data
Date_R shows 02/02/2010
Received shows Tick
ID shows 2
Main shows 1

Record 2 02/02/2010, Tick, 2, 2


Table B has the following fields and data types


IDNo >>> AutoNumber >>>PK Field
Main >>> Number
Colour>>> Text

Record 1 has the following data
IDNo shows 1
Main shows 1
Colour shows Y

More Background

Relationships is one to many

Table A set to main
Related table/ query set to main

Join properties option 1

Enforce ... selected

 

[Marshall Barton]

I tried the suggested update to my query but unfortunately it did not
display the correct result.

Hence to simplify fault finding the problem. I created two tables, a query
and a form / subform based on the example code we have been discussing.
Using test data so that Record 2 of table A would be flagged as it did not
have a Y in the colour field but did have a tick in Received field resulted
in no record returned by the query.
(No record returned at all, of any type)


I have supplied the following information as I must be overlooking
something.

Copy and pasted

SELECT A.Date_R, A.Received, A.ID, B.Colour
FROM A INNER JOIN B ON A.Main = B.Main
WHERE A.Received=-1 AND B.Colour <>"Y";

Table A has the following fields and data types
Date_R >>> Date/Time
Received >>> Yes/No
ID >>> Text
Main>>> AutoNumber>>> PK Field

Record 1 has the following data
Date_R shows 02/02/2010
Received shows Tick
ID shows 2
Main shows 1

Record 2 02/02/2010, Tick, 2, 2

Table B has the following fields and data types
IDNo >>> AutoNumber >>>PK Field
Main >>> Number
Colour>>> Text

Record 1 has the following data
IDNo shows 1
Main shows 1
Colour shows Y

[snip]


Your query will not return any records because tblA record 1
joins with rblB record 1 but is filtered out because colour
= Y.

tblA record 2 does not join to a record in tblB so the
query's colour field contains Null. It is very important to
understand that Null kind of represents that the value is
**unknown**. As such, you can not ever say that an unknown
value is either equal or not equal to any other value, not
even another unknown value. Do this record is filtered out
because you can not say that a Null colour is not equal to Y

If you want to return records from tblA when the colour fiel
is Null, then you have to do something to allow for that.
This is one way:

WHERE A.Received=-1 AND (B.Colour <>"Y" Or B.Colour Is Null)

Note that those parenthesis are needed to get the And and Or
to be evaluated in the right order.

 

[KARL DEWEY]

I tried the suggested update to my query but unfortunately it did not
display the correct result.
What was the results? Can't fix without knowing what's wrong.

What was the output?

 

[jo]

Hi, Marsh

I tried your example unfortunately it did not return any records from Table
A when the colour field is Null in Table B
(No records were return at all)

 

[Marshall Barton]

I tried your example unfortunately it did not return any records from Table
A when the colour field is Null in Table B
(No records were return at all

Your example had no records in Table B with Null in the
Colour field so I'm not clear about what you did.

OTOH, if you added such a record, I would expect the query
to find it, so I guess I need to see the query as you tried
it.

You might want to try debugging the query by removing
various parts of the where clause to verify each condition
separately.

 

[jo]

Hi, again

All I did was to replace your suggested “where” state in my query as below,
and then rerun it using the current data in the test database as already
lisedt within these threads.

SELECT A.Date_R, A.Received, A.ID, B.Colour
FROM A INNER JOIN B ON A.Main = B.Main
WHERE A.Received=-1 AND (B.Colour <>"Y" Or B.Colour Is Null);


If you are suggesting that my approach is on the right lines, I will try
debugging the query over the weekend and get back to you.

Thank you for you guidance so far.

 

[Marshall Barton]

All I did was to replace your suggested “where” state in my query as below,
and then rerun it using the current data in the test database as already
lisedt within these threads.

SELECT A.Date_R, A.Received, A.ID, B.Colour
FROM A INNER JOIN B ON A.Main = B.Main
WHERE A.Received=-1 AND (B.Colour <>"Y" Or B.Colour Is Null);


If you are suggesting that my approach is on the right lines, I will try
debugging the query over the weekend and get back to you.

Sheesh, I had a blind spot, sorry.

Try changing the INNER JOIN to a LEFT JOIN

 

[jo]

Hi
All up and running now.

Thank you for your assistance.