A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Obtaining the correct results from a two table query



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 09:22 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 6
Default Obtaining the correct results from a two table query

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”));
  #3  
Old May 19th, 2010, 10:21 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 6
Default Obtaining the correct results from a two table query

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"));
  #4  
Old May 20th, 2010, 12:10 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Obtaining the correct results from a two table query

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");

--
Build a little, test a little.


" wrote:

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"));
.

  #6  
Old May 20th, 2010, 08:15 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 6
Default Obtaining the correct results from a two table query

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
  #7  
Old May 20th, 2010, 10:32 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Obtaining the correct results from a two table query

wrote:
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.

--
Marsh
MVP [MS Access]
  #8  
Old May 20th, 2010, 10:59 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Obtaining the correct results from a two table query

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?

--
Build a little, test a little.


" wrote:

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
.

  #9  
Old May 21st, 2010, 03:13 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 6
Default Obtaining the correct results from a two table query

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)
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.