?
Solved

Access query compare does not work using Not in or not equal

Posted on 2014-04-14
9
Medium Priority
?
1,241 Views
Last Modified: 2014-04-15
In an Access db I have forms based on queries.  One of the queries is supposed to return data but not data that was in the other query.  I join the queries and tried Not In, <> and Is Null but do not get the expected results.  I am wondering if having one of my fields named "Value" is an issue because it is a reserved word?  Because of that I have the initial query run and build a table that contains only only one field with unique results.  The next query should return results that are valid but exclusive of those returned in the first query.  How do I get results that are unique among the two queries?

TIA
Tim
0
Comment
Question by:gibneyt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39999216
Although you should try to avoid using reserved words as column headers in your queries, you can minimize the effect of that by wrapping the column name in brackets [Value] whenever you refer to that column.

To do what you are asking you would normally use a query that looks something like:

SELECT TableA.*
FROM TableA
LEFT JOIN TableB
ON TableA.[SomeField] = TableB.[SomeField]
WHERE TableB.SomeField IS NULL

This would give you all the records in TableA which do not have a record in TableB that matches on the [SomeField] column.
0
 

Author Comment

by:gibneyt
ID: 39999341
fyed,

Thanks for the quick response.

I do believe that is what I am trying to do.  My initial question was based on trying other things to make what you are suggesting work, and I had been trying from the beginning.  So here is the SQL that should work.

SELECT tblHLM_BOM.MFG_PN, tblHLM_BOM.MFG, tblMAX_MPM.MPNNUM_49, tblMAX_MPM.MPNMFG_49, tblHLM_BOM.WW_PN, tblMAX_PM_CAP.PRTNUM_01, tblHLM_BOM.VALUE, tblHLM_BOM.VOLTAGE, tblHLM_BOM.TOL, tblHLM_BOM.JEDEC_TYPE, tblMAX_PM_CAP.VALUE, tblMAX_PM_CAP.VOLTAGE, tblMAX_PM_CAP.TOLERANCE, tblMAX_PM_CAP.PACKAGE, tblMAX_PM_CAP.PMDES2_01, Right([tblPCB_Number]![ITEM#],Len([tblPCB_Number]![ITEM#])-17) AS PCB_NUMBER, Right([tblPCB_Name]![ITEM#],Len([tblPCB_Name]![ITEM#])-10) AS PCB_NAME, tblHLM_BOM.DESCRIPTION, qryExactMatches.WW_PN
FROM tblPCB_Number, tblPCB_Name, ((tblHLM_BOM INNER JOIN tblMAX_PM_CAP ON (tblHLM_BOM.VALUE = tblMAX_PM_CAP.VALUE) AND (tblHLM_BOM.VOLTAGE = tblMAX_PM_CAP.VOLTAGE)) INNER JOIN tblMAX_MPM ON tblMAX_PM_CAP.PRTNUM_01 = tblMAX_MPM.PRTNUM_49) LEFT JOIN qryExactMatches ON tblHLM_BOM.WW_PN = qryExactMatches.WW_PN
WHERE (((tblMAX_PM_CAP.PRTNUM_01) Not Between "7001864" And "7008605") AND ((qryExactMatches.WW_PN) Is Null));

With this, Access returns an error about ambiguous outer joins.  If I change to an inner join I don't get an error nor do I get ANY results!  I use the very same Left Outer join and "Is Null" for another query/table compare/exclude and it works perfectly.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39999462
The ambiguous outer joins error has to do with the fact that you are using a a cartesian join, an inner join and a LEFT JOIN, in the same query.  You can have the inner and left joins, as long as they are joined in the right sequence.  For example, you can do:

TableA  INNER JOIN TableB INNER JOIN TableC LEFT JOIN TableD

but you generally won't be able to do:

TableA  INNER JOIN TableB LEFT JOIN TableC INNER JOIN TableD

I would recommend that you create a subquery to join tblPBC_Name and tblPBC_Number, but it doesn't appear that you are joining either of those tables to the other tables/ queries in your query.  If those are only returning a single value, I would recommend either a Dlookup() or an embedded Select statement to get those values.

You might want to try removing those two tables and the associated computed columns from the query and see if it works without them.

Also, you may get some strange results using BETWEEN with text strings, especially with the NOT operator.  You might want to consider:

WHERE ([PRTNUM_01] < "7001864" OR [PRTNUM_01] > "7008605")

keeping in mind, that "8" is greater than "7008605"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gibneyt
ID: 39999833
I reworked the query so that qryExactMatches-All returns all matching parts and it works.  Here's the SQL:

SELECT tblHLM_BOM.WW_PN, tblMAX_PM_CAP.PRTNUM_01, tblHLM_BOM.MFG_PN, tblHLM_BOM.MFG, tblMAX_MPM.MPNNUM_49, tblMAX_MPM.MPNMFG_49, tblHLM_BOM.VALUE, tblHLM_BOM.VOLTAGE, tblHLM_BOM.TOL, tblHLM_BOM.JEDEC_TYPE, tblMAX_PM_CAP.VALUE, tblMAX_PM_CAP.VOLTAGE, tblMAX_PM_CAP.TOLERANCE, tblMAX_PM_CAP.PACKAGE, tblHLM_BOM.DESCRIPTION, tblMAX_PM_CAP.PMDES2_01, Right([tblPCB_Number]![ITEM#],Len([tblPCB_Number]![ITEM#])-17) AS PCB_NUMBER, Right([tblPCB_Name]![ITEM#],Len([tblPCB_Name]![ITEM#])-10) AS PCB_NAME
FROM tblPCB_Name, tblPCB_Number, (tblHLM_BOM INNER JOIN tblMAX_PM_CAP ON (tblHLM_BOM.VOLTAGE = tblMAX_PM_CAP.VOLTAGE) AND (tblHLM_BOM.VALUE = tblMAX_PM_CAP.VALUE)) INNER JOIN tblMAX_MPM ON tblMAX_PM_CAP.PRTNUM_01 = tblMAX_MPM.PRTNUM_49
WHERE (((tblMAX_PM_CAP.PRTNUM_01) Not Between "7001864" And "7008605"));

I created another query Left joining qryExactMatches to qryCAP_Matches-All on WW_PN.  Here's theSQL

SELECT [qryCAP_Matches-All].WW_PN, [qryCAP_Matches-All].PRTNUM_01, [qryCAP_Matches-All].MFG_PN, [qryCAP_Matches-All].MFG, [qryCAP_Matches-All].MPNNUM_49, [qryCAP_Matches-All].MPNMFG_49, [qryCAP_Matches-All].tblHLM_BOM.VALUE, [qryCAP_Matches-All].tblHLM_BOM.VOLTAGE, [qryCAP_Matches-All].TOL, [qryCAP_Matches-All].JEDEC_TYPE, [qryCAP_Matches-All].tblMAX_PM_CAP.VALUE, [qryCAP_Matches-All].tblMAX_PM_CAP.VOLTAGE, [qryCAP_Matches-All].TOLERANCE, [qryCAP_Matches-All].PACKAGE, [qryCAP_Matches-All].DESCRIPTION, [qryCAP_Matches-All].PMDES2_01, [qryCAP_Matches-All].PCB_NUMBER, [qryCAP_Matches-All].PCB_NAME, qryExactMatches.WW_PN
FROM [qryCAP_Matches-All] LEFT JOIN qryExactMatches ON [qryCAP_Matches-All].WW_PN = qryExactMatches.WW_PN
WHERE (((qryExactMatches.WW_PN) Is Null));

This works and returns expected results and none listed in qryExactMatches.

Trouble is when I run the report (after repointing all fields with the green mark) I get the error "The specified field 'WW_PN' could refer to more than one table listed in the FROM clause of your SQL statement.'
0
 

Author Comment

by:gibneyt
ID: 40000215
So the report I tried to use was an old one.  I rebuilt the report with the Wizard and it now works.  Thanks so much for the pointers the did result in correcting my issues.  A+.

Tim
0
 

Author Closing Comment

by:gibneyt
ID: 40000221
Breaking apart the queries did the trick and then rebuilding the report with the wizard displayed the expected results.  Thanks so much fyed.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40000333
glad I could help
0
 

Author Comment

by:gibneyt
ID: 40002454
fyed, you mentioned DLookup above to get to data for single records.  What would that line look like in the form text box Control Source?  Right now I have =DLookUp([item#],[tblPCB_Name]) but it is returning #Name?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40002730
you would have to wrap the reference to the field and table in quotes:

=DLOOKUP("Item#", "tblPCB_Name")
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question