Solved

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

Posted on 2014-04-14
9
1,189 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
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…

757 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

20 Experts available now in Live!

Get 1:1 Help Now