Solved

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

Posted on 2014-04-14
9
1,205 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
  • 5
  • 4
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

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

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now