Excluding data from a query (2000)

[Lucy]

Hi - don't use Access very often, not technically minded and its early in the morning - can you help please?
I have a table of Supplier Details which contains records for suppliers of products listed in a number of different tables. I need to extract a list of the suppliers who provide products listed in a table, but unfortunately that table does not have a Supplier ID field. The other tables do have Supplier ID fields. How can I create a query that will return only the records that DO NOT match a Supplier ID on any of the other tables?

Thanks
Lucy

[HansV]

Lucy,

Can you provide more details?

- Which table doesn't have a Supplier ID field?
- Is there a Product ID field or something like that on which records from different tables can be matched?
- You want to return records that do not match a Supplier ID in any of the other tables (my italics). How many other tables are there, what are they called, ...

[Lucy]

Hans

I have two databases - one called Books, the other called Serials. Each has a table with the same name respectively. There is a table in one database which is linked to the other, which is called Supplier Details. This table contains all the contact details for suppliers of Books and Serials. The Books table doesn't have a supplier ID field in it, but the Serials and Supplier Details tables do. Unfortunately I need to get a list of all suppliers of Books. Is this any clearer? I know this is not a good set up but I have inherited it and haven't had time to tidy it all up yet and the list can't wait until I do.

Thanks
Lucy

[HansV]

If the Books table does'n't have a Supplier ID field, what other field or fields could be used to match the records in the Books table with records in the Supplier Details table?

[Lucy]

Hans

There are no fields that could be used to match the Books table to the Supplier Details. That's why I need to be able to match the Supplier Details and Serials tables, and having done that identify the records that were not matched. The records that do not match will be the suppliers of Books.

Thanks
Lucy

[HansV]

Ok, you can use the Find Unmatched Query Wizard to do this.
<UL><LI>Activate the Queries tab of the database window.
<LI>Click the New button at the top of the database window.
<LI>Select Find Unmatched Query Wizard, then click OK.
<LI>In the first step, select the table Supplier Details, then click Next.
<LI>In the second step, select the table Serials, then click Next.
<LI>In the third step, click the Supplier ID field in both tables, then click the <=> button to specify that you want to compare the tables on these fields. Then click Next.
<LI>In the fourth step, specify the fields you want to see; if you want to include them all, click >>. Then click Next.
<LI>In the final step, Access proposes a name for the query. You can accept this or modify it as you like. Then click Finish.[/list]The resulting query will display records in Supplier Details without a matching record in Serials. If you want to modify it, open the query in design view.

[Lucy]

It's so easy when you know how. Thanks a million.

Lucy