Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-04-14
9
Medium Priority
?
1,252 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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