AND function in a single criteria in Access/Excel

[musicfreak1203]

I have a table with company, products, local product and country.

I want to search that if a company has particular two products then display company, products, local product and country.

For example.,
If company A has pencil and eraser then display all the records.

[twinnyfo]

musicfreak1203,

Welcome to Bytes!

Your question is a bit vague, but I think I understand what you are asking.

The theoretical answer is that you will want to use a sub-query that finds all the comapnies that have those two products. However, as I have found in the past, finding a company that has "either" of those products is easy. Finding the company that has "both" is more challenging.

However, your query could look something like this:

SELECT * 
FROM tblCompanyProducts 
WHERE Company IN ( 
    SELECT Company 
    FROM tblCompanyProducts 
    WHERE Products = "Pencil") 
AND Company IN (
    SELECT Company 
    FROM tblCompanyProducts 
    WHERE Products = "Eraser") 
ORDER BY Company, Products;

Hope this hepps!

[NeoPa]

Two points :

1. If your one table really has those fields in it then I suggest you throw it away and start again using more conventional design techniques (See Database Normalisation and Table Structures).
2. I won't delete the question due to its being too inadequate, but only because it already has an answer. It really isn't acceptable to to post questions on here that are simply throwing a couple of sentences together. If you want people to spend their time helping you then a few minutes of proper preparation isn't an unreasonable expectation. It's actually a site requirement anyway.