Solved

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

Posted on 2014-04-14
9
1,220 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

761 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