423,846 Members | 2,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,846 IT Pros & Developers. It's quick & easy.

AND function in a single criteria in Access/Excel

P: 1
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.
1 Week Ago #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,703

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblCompanyProducts 
  3. WHERE Company IN ( 
  4.     SELECT Company 
  5.     FROM tblCompanyProducts 
  6.     WHERE Products = "Pencil") 
  7. AND Company IN (
  8.     SELECT Company 
  9.     FROM tblCompanyProducts 
  10.     WHERE Products = "Eraser") 
  11. ORDER BY Company, Products;
Hope this hepps!
1 Week Ago #2

Expert Mod 15k+
P: 31,121
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.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.