run query from 2 tables where value is null in 1 of the tables

[abrown]

I have a combo box on my form where the user can lookup new requests from a
table that is linked to another database. Once the request is worked it is
saved into a table within this database; therefore, I don't want
worked/completed requests to show up in the "new request" lookup combo box.
I have the combo box linked to a query that is pulling in certain values
from the external database table...how can I exclude requests that have
already been worked and are present in the current database table from this
query?

Thanks,
Amber

 

[Klatuu]

If your combo box is based only on the external database where new requests
are housed, then worked requests should not even show up in the combo unless
the worked records remain in the external database for some time. Then you
would need a field in the external table to indicate the request status and
filter your combo on that status.

 

[abrown]

Yes, the combo box is based only on the external db, but all requests will
always be present. I'm just using the external db to pull some of the
information in so the user will have less keystrokes. Is there not another
way to exclude requests that are in the current db from the list I'm pulling
from the external db without having to create another field in the external
db table?

 

[Klatuu]

Yes, you could use a subquery. Not knowing your field names, this is only an
example, but the field should be the table's primary key or some other field
that is required to be unique:

SELECT UniqueID FROM ExternaTableName WHERE NOT IN (SELECT UniqueID FROM
LocalTableName);

 

[Klatuu]

In my previous post I said it had to be a unique field. That may not be
true, depending on your data. It might be you can use DISTINCT, but with
data from two tables, you would need to do some testing to ensure you don't
either include or exclude records