Query that show missing Items

[nxqviet]

Hi all,

I have a question about a select query. I have a table that contains a
long list of product, and another table that contains an incomplete
listing of the same products (a partial list that has only selected
products).

I want to have a query that will check the second (the incomplete list)
table and return items that are missing in this list. Ofcouse the items
will have to be pulled from the comprehensive list.

I tried to use Not In (Table refference) but it did not work. I don't
know if there is a better way to do just this. Please help.


V_

 

[Al Campagna]

nx,
Given examples...
Table1 = Complete list
Table2 = Partial
LinkField = ProductNO
Actually your trying to find Table1 ProductNos that have no match in Table2

Create a query that relates Table1 to Table2 via ProductNo, and select "Show ALL in
Table1 and only those that match in Table2."
Put Table1.ProductNo and Table2.ProductNo in the query grid
Run the query...
Any Table1 that has no matching Table2 entry will show blank (null) in the
Table2.ProductNo column.
So, a criteria of
Is Null
against Table2.ProductNo will display ONLY the ProductNi information you want. You could
then use that query to create a Delete query.

BUT... you wrote...Couldn't you just delete all the records in Table1 and append Table2 records into it, or
delete Table1 and copy Table2 to Tbasle1?

 

[nxqviet]

Al,

Actually that is a great idea, I'll try that. I can also use an Append
Query to bing all these Items into a new table, then use a Delete Query
to delete all the Extra items in this new table. This way, I'll not
have to delete any item in the comprehensive list.

I'll try and follow up if I have any additonal questions.

Thank you very much,


V_

 

[nxqviet]

Al,

I tried it and I inserted a criteria for the items from smaller list as
(IS NULL). This show only the items that is "Missing." It worked great,
Thanks again.

V_