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�));
0
jo
5/19/2010 8:22:07 PM
access.queries 6343 articles. 1 followers. Follow

12 Replies
1267 Views

Similar Articles

[PageSpeed] 19

jo@jo.uk wrote:

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


Did you Copy/Paste that query or retype it?

It looks like it would do what you asked if you fixed the
bad table and field names and the unbalanced Parenthesis.

Maybe the Join should be INNER JOIN so you know there sould
be a B.Color value to check.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/19/2010 8:47:01 PM
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"));
0
jo
5/19/2010 9:21:43 PM
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.


"jo@jo.uk" 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"));
> .
> 
0
Utf
5/19/2010 11:10:01 PM
jo@jo.uk 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"));


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.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/20/2010 2:32:38 AM
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
0
jo
5/20/2010 7:15:48 PM
jo@jo.uk 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]
0
Marshall
5/20/2010 9:32:17 PM
>>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.


"jo@jo.uk" 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
> .
> 
0
Utf
5/20/2010 9:59:01 PM
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)
0
jo
5/21/2010 2:13:29 PM
jo@jo.uk wrote:
>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.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/21/2010 2:56:44 PM
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.
0
jo
5/21/2010 3:29:41 PM
jo@jo.uk wrote:
>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

-- 
Marsh
MVP [MS Access]
0
Marshall
5/21/2010 4:36:10 PM
Hi
All up and running now.

Thank you for your assistance.
0
jo
5/24/2010 5:01:23 PM
Reply:

Similar Artilces:

pivot tables 02-02-10
So in 2007, I have two pivot tables that reference the same data set and have the same report filters. Is there a way to set it up so I only have to change the report filter selection on one pivot table and the data will be updated in both? I am fairly comfortable with VBA so any solution/suggestion is welcomed. Thanks for any help. Yes, you seem to want, "Synchronizing Two Pivot Tables with One Combo Box" covered in detail in pages 225-229 of Pivot Table Data Crunching by Jelen / Alexander. "Mike" wrote: > So in 2007, I have two pivot tables th...

adding events to form base on table
Hello there I have subform which is bound to table an not to form Is there a way to add events like OnDblClick to fields? On Mon, 16 Jul 2007 17:53:40 +0300, "Roy Goldhammer" <roy@hotmail.com> wrote: >Hello there > >I have subform which is bound to table an not to form > >Is there a way to add events like OnDblClick to fields? > OnDblClick IS an event on each of your form fields. You can add whatever code you need to that event. How can i add this event to subform when it is a table and not a form? "Lynn Trapp" <noltrapp@spamltcomputerd...

How to put a parameter into an Excel Query
I am creating an external data query in excel to reterive data from a SQL database, one of the fields in the db is a date. When I run the query I want Excel to asked me for a date range for the data to return. Can any one tell me what I have to enter into the MS Query Editor in the query criteria area to bring up a window that asks for the start and end date of the data that it will reterive? Thanks in advance for any assistance This website will explain what you need to know: http://www.rdg.ac.uk/ITS/info/training/notes/excel/query/ Does that help? *********** Regards, Ron XL2002...

Font not displaying correctly after upgrade from '00 to '03
Hi everyone. I hope someone can help me! I have a Publisher file that was created in Publisher 2000 using Heather font. Since we bought new computers and updgraded to Office 2003, when I went to update the file, I saw that the font is not displaying correctly. I tried copying the font file from the old computer where it still shows up correctly, but that didn't work. I also tried to save the file as a Publisher 2000 file, but when I opened it again it still didn't display right. Any way to avoid this? Thanks!!! Amy <Amy@discussions.microsoft.com> was very recently heard t...

OL 2010: subfolders not sorting correctly
I'm not exactly sure what I did, but after I did "it," I found that one of my subfolders is sorted out of order. Even after I ran scanpst, the problem is still present: Here is a partial list of subfolders: Sales Process Search Folders Sent Items 2010 Status <- obviously out of order. Strategy Until "it" happened, the "2010 Status" folder was properly grouped with other folders whose name started with "2010." Now I can drag 2010 Status into another folder as a subfolder. Then if I MOVE the folder back to the main folder, 2010 Status sorts b...

OWA on two servers and with two mail domains
Hi Everybody. My setup is this: We have an Exchange Server 2000 on location A. We have an Exchange Server 2003 on location B. Both servers are domain controllers in the domain "test.domainname.com". The Exchange Server on location A handles the mail domain "domainname.com". The Exchange Server on location B handles the mail domain "othername.com". We have only one Recipient Policy "Default Recipient Policy" into which we have put "domainname.com" (primary) and "othername.com". We are using Outlook Web Access. The users on locat...

UserForm Query
UserForm Query I have a User Form which is used to collect monthly cost information for a five year period for a database. The start month and year of the five year period will vary from record to record. Intially the user enters the start month and start year into separate Text Boxes. From this information I have created an Array, strMonthLabels(60), which contains each month and year for the five year period; i.e. "Jan 2009", "Feb 2009", etc. Each of the Text Boxes to capture the month cost information has an adjacent Label to signify the month an...

See this correction package
--zakubwskekxmnxp Content-Type: multipart/related; boundary="nsezmhxzfam"; type="multipart/alternative" --nsezmhxzfam Content-Type: multipart/alternative; boundary="qzxezthhxitrov" --qzxezthhxitrov Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft User this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to mai...

How to detect that a range object is in a table?
Hi, How to detect that a range object is in a table? Thanks, Hi mjlaali, ..Range.Information(wdWithInTable) -- Cheers macropod [Microsoft MVP - Word] "mjlaali" <mjlaali@discussions.microsoft.com> wrote in message news:CCBB2AA7-D669-4233-80FA-F0B49C27C009@microsoft.com... > Hi, > > How to detect that a range object is in a table? > > Thanks, ...

Unable to use two licences on two linked computers
Hey all, I have OS10.4.9 in my business mac network, and I installed Office X on the server and the reception computer. Initially it was the same licence and as others have described, I could not use both programs at the same time. However to improve our office efficiency, I bought a new copy of Office X today, and installed it. However it still won't let me use both versions at the same time even though now I have two licences! Why is this? The Microsoft website is not forthcoming, and they don't have any help lines to call. I can't find anywhere to input my new product key,...

Animation query
I'm building a program that animates a ball moving around the screen. The ball is going to be a simple object, probably not much more than a small white circle. I haven't tried it yet, but am just trying to guage opinion from people who have done this sort of thing on what would be the best method to start investigating. I am planning on kicking off a thread to handle the repainting of the ball in a different position, and then letting that thread Sleep for a certain amount of time between frames, the time it would Sleep for would be guaged by how fast the ball is moving. I ju...

% increase when previous result is 0
We have a scoring system at work that measures our team's success by % increase on last year, for various functions. Each of 9 teams is then allocated points , 8 for biggest % increase, down to 0 for lowest For a particular KPI, my last year my team had a zero result, this year we're on 3. The formula can't handle 0 as a starting point, so we end up with 0 points, and will no no matter how well we do this year... So, in the real world, what is our poercentage increase if we had 0 last year, 3 this year ? In excel, what is the fairest formula to calculate this ? Don't r...

Obtaining CWnd
I using a function to get information about the current DC. In this case, I want to grab the current DC and save it off so that I might modify it to retrieve information about a particular font. CClientDC wants a CWnd *. Previously, I would save a ptr to the main application window but I'm trying to provide a better encapsulation so I don't have to rely on an external ptr of sorts. Is there an API call that will allow me to get the pointer to the main application (process)? This is a scaled down implementation of the function - CSize DisplayHandler::TextDimension(const TCHAR *T...

Pivot Table Wizard causes crash
Hello, I'm using Excel 2003(11.8316.8221) SP3 When I use the Pivot Table Wizard and press the "back" button to redefine the range of data. Excel crashes. I've tried with no other files open. I can use the wizard to create now pivot tables, but not redefine this existing PT. The file is about 1 meg. Thanks in advance. Mike Just after posting this I found a reference to the problem being the result of having "frozen windows" on the sheet with the PT. I "unfroze" the windows and everything works just fine. Mike "mike in...

Obtaining variables during a userform_initialize event
I am using the worksheet_beforedoubleclick event to open a userform. During the userform_initialize event I need to obtain value of the 'target' arguement that is referenced in the worksheet_beforedoubleclick event. How can I obtain this value? Do I need to go with a public variable? --- Message posted from http://www.ExcelForum.com/ It depends on how and where you are going to use the variable populated with the target.value --- Message posted from http://www.ExcelForum.com/ ...

Copy range of cells omitting formulas that result in " "
I have a column of data that contains a formula that either returns a value or leaves the cell blank. I would like to copy the column to another location and have the cells with no values omitted from the range. Basically, I would like to make a bar chart and have the chart adjust with changes in data. However, every empty cell is plotted as zero or blank...how can I remove these blanks from the chart? Can anyone help? I have been struggling with this for a while now...I think this is my last resort! Help! Tina Select all of the data and plot it. then by using auto filter on your dat...

Help with a simple query
Hi guys and gals, Can anyone help me with this query? I am having some issues with pulling in all names even though (it appears) I have the join correct. SELECT tblEmployee.EmpRptID, tblEmployee.UserName, Sum(tmpReports.NumOfSets) AS SumOfNumOfSets FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName = tmpReports.UserName WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName; What am I doing wrong? You have defeated the left join by applying criteria to the right s...

Apply these corrective patch from the MS Corporation
--isxvrerlsechesce Content-Type: multipart/related; boundary="yoxqveduchrdaidl"; type="multipart/alternative" --yoxqveduchrdaidl Content-Type: multipart/alternative; boundary="aqavdfgcddhoipcv" --aqavdfgcddhoipcv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now...

Error Message (Same table, cannot be the the child of two nested relations)
We created a XML Schema that has nested table relations. We had the following Warning message come up. An unhandled exception of the type 'System.ArgumentException' occurred in system.data.dll Additional information: The same Table (DOS_Identifiers) cannot be the child table in two nested relations. Does anyone know a workaround for this problem? You are suppose to be able to do this according to the standard from W3C. HELP! Thanks in advance -={Brian Kedersha}=- bkedersha@aol.com kedershab@cox.net Brian, It's an acknowledged limitation of the DataSet [0]. The linked po...

Adding cells to pivot table
Is it possible, once a pivot table is created, to add a few more rows of source data to the original data range? Stephen You can use a dynamic data source for the pivot table. There are instructions here: http://www.contextures.com/xlPivot01.html Stephen Boulet wrote: > Is it possible, once a pivot table is created, to add a few more rows of > source data to the original data range? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks for the hint ... that will work in a pinch; I just need to remember to set up the table with a ...

FW: Prove that correction package
--gltfpsvthyxa Content-Type: multipart/related; boundary="kduhlpjc"; type="multipart/alternative" --kduhlpjc Content-Type: multipart/alternative; boundary="ucbftrhr" --ucbftrhr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to continue keeping your computer secure from these vulnerabilities, the ...

GETPIVOTDATA query
The formula below works. It gets the VAT value from the pivot table for TP = 1. GETPIVOTDATA($B$5,"TP['1'] VAT") Is there a way that I can reference the TP outside the formula - something like GETPIVOTDATA($B$5,"TP[' & A1 & '] VAT") so that I can choose which TP to see by changing the value in A1. Thanks Laurence Lombard Add some quotation marks, and it should work: =GETPIVOTDATA($B$8,"TP " & A1 & " VAT") It should work without the square brackets and single quotes around the number. Laurence Lombard wro...

Updating two two tables based upon the response to questions in a subform
Me again. I'm trying to populate two tables based upon the results to questions on a subform. I have a form and one subform. The subform has a variable number of questions related to the form. Each question is limited to either "Pass", "Fail", or "N/A". If the user should select "Fail" on a question, I want to add a record to a table called "tblCARs" and a record to a table called "tbl CARLineItems". However, for every subsequent "Fail" response I want (need) to only add a record to the table "tblCARLi...

Counting sets of cells based on two parameters
Hello, We have a spreadsheet that is layed out something like this: 6 Collumns. Collumn A contains certain features of a software product. Collumn C contains X's, as checkmarks (indicating those features are present). Collumn F contains comments regarding the features in collumn A. The table looks something like this: A C F feat1 X comment feat2 comment feat3 X What we need to do is count the NUMBER OF ROWS that contain BOTH an X in collumn C, and a comment in collumn F. I know how to coun...

Problems in the string articlexml obtained
string strColumnSetXml = "<columnset>"; strColumnSetXml += "<column>title</column>"; strColumnSetXml += "<column>description</column>"; strColumnSetXml += "<column>number</column>"; strColumnSetXml += "<column>articlexml</column>"; strColumnSetXml += "<column>modifiedon</column>"; strColumnSetXml += "</columnset>"; // Retrieve the article return article.Retrieve(userAuth, articleId, strColumnSetXml); When i execute the code above, in the column xml i o...