Queries

[Guest]

I have a table "Suppliers" which has column "SupplierID", when I add products
the SupplierID is written to another table "Products". What I would like to
write is a query where the SupplierID IS in Suppliers but not in Products,
thus giving me a list of suppliers who do not have any products listed
against them.
Can anyone help, thanks in anticipation.

 

[Arvin Meyer [MVP]]

How about:

SELECT DISTINCT Suppliers.ID, Suppliers.SupplierName
FROM Suppliers LEFT JOIN Products ON Suppliers.ID = Products.ID
WHERE Products.ID Is Null;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Just found my own answer using the built in Cross Tab Query,
Cheers

 

[Graham R Seach]

Allen,

This should do:

SELECT * FROM Suppliers
LEFT JOIN Products
ON Products.SupplierID = Suppliers.SupplierID
WHERE Products.SupplierID IS NULL

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Guest]

Hi Allan,

Click on the query under the objects.Click once the new query. You will have
many queries, such as Design View, Simple Query Wizard. In that, the last one
would be, Find Unmatched Query Wizard. Click on that and follow the
procedure. you will get the results.

If you need more info on how to proceed, please do get in touch.

Thanks
Have a Nice Day.
Maran

 

[Guest]

Thanks to everyone who gave a response, I have the problem sorted.

Cheers