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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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”)); |
#2
|
|||
|
|||
Obtaining the correct results from a two table query
|
#3
|
|||
|
|||
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
|
|||
|
|||
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")); . |
#5
|
|||
|
|||
Obtaining the correct results from a two table query
|
#6
|
|||
|
|||
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
|
|||
|
|||
Obtaining the correct results from a two table query
|
#8
|
|||
|
|||
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
|
|||
|
|||
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) |
#10
|
|||
|
|||
Obtaining the correct results from a two table query
|
|
Thread Tools | |
Display Modes | |
|
|