Combo box based on a table to exclude data from second table

[magicdds-]

I have a combo box that is based in a table. The bound column is IDnumber. I
have a second table that lists some of the IDnumbers.

I would like to change the combo box so that it lists only the IDnumbers
from Table1 that are not listed in table2. Is there a way to create a query
to get only those Idnumbers from Table1 that are not listed in Table2?

Thanks for any help.
Mark

 

[pietlinden]

I have a combo box that is based in a table. The bound column is IDnumber.. I
have a second table that lists some of the IDnumbers.

I would like to change the combo box so that it lists only the IDnumbers
from Table1 that are not listed in table2. Is there a way to create a query
to get only those Idnumbers from Table1 that are not listed in Table2?

Thanks for any help.
Mark

SELECT table1.field1
FROM table1
WHERE table1.field1 NOT IN (SELECT table2.field2 FROM table2);

 

[a a r o n _ k e m p f]

or you can do this same thign with joins and the where clause.

I can't promise my aircode but it's something like this

Select O.*, E.*
From Orders O Right Join Employees E On O.SalesPersonID = E.EmployeeID
Where O.OrderID IS NULL

This would give you a list of employees that don't have orders.
This join style (as opposed to the subquery) works better once you
start getting a large number of rows.

 

[Tom Wickerath]

Aaron,

Your unmatched query is a good approach, but why would you use the SELECT *
approach? That seems to be a waste of network resources, especially if there
are a lot of fields in each table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.