By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 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.
Dec 6 '18 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 3,284

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!
Dec 6 '18 #2

Expert Mod 15k+
P: 31,494
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.
Dec 6 '18 #3

Post your reply

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