Category vs. Company

[prometro]

Hi,
I cant find solution for this problem :

I would like to sort companies by several categories.
Every company can be in more categories.
It means that one category can be common for more companies.

There is an example of table where are companies and categories together :
Each category is here representing by a number :

Name of company category number
Company1 1
Company1 2
Company1 3
Company 2 1
Company 2 3
Company3 4

My problem is :
If I do query to find companies with category number 1 and 2 and 3 the result is Company1 and Company2. It is ok, BUT I dont know how to do query which can find company which has category 1 and also 2. The result at this moment must be only Company1.

I know that I can do it with step by step query - the first find all companies with category1 and the second query find all companies with category2 from the result of the first query. But I dont know how to do it when the number of choosed cathegories in search form will difrent all the time - so I cant know in advance how much queries I will need...

If you have idea, pleas help me with it.
Thank you
Jiri

 

[vbaInet]

In your query design, under the Category Number field, in the Criteria box you put:

1 OR 2

 

[prometro]

Ok, it works - it find all companies which have category 1 or 2.
But there is also another part of searching :
Find only those companies which have category 1 and 2 at the same time...

 

[vbaInet]

It's either an OR or an AND in your case. It doesn't make sense to search for 1 and 2 AND 1 or 2. Unless you want to search for 1 or 2 AND 3.

 

[vbaInet]

I think I understand where you're coming from now! You want to search for companies that belong to BOTH Categories 1 and 2 right? So if a company belongs to category 1 but doesn't belong to category 2 it will not be included?

 

[prometro]

I am not sure, if I explain it well :
If I have for example this table (id, CompanyName, Category) and I have five records with only two companies CompanyA and CompanyB :

1 ; Company A; Category1
2 ; Company A; Category2
3 ; Company A; Category3
4 ; Company B; Category1
5 ; Company B; Category3

So, company A has folowing categories : 1;2;3
and company B has only two categories : 1;3

What I need to do If I want to find company which is exactly in categories 1;2;3 ?
Result is of course only company A...
Jiri

 

[prometro]

... yes!, You answer me more quickly then I finish my text

 

[vbaInet]

Quick thought. You need to:

1. Use 1 OR 2 OR 3
2. Create another query that will perform a COUNT of how many times a Company appears. This new query will be based on the result from query in step 1.
3. If it appears 3 times, then that company belongs to 1 and 2 and 3.

Edit: I changed the AND to OR.

 

[prometro]

Hmm, I understand and it sounds great! I will continue later with this work and then I will send you result! Thank you again.
Jiri

 

[vbaInet]

Just post back if you require further help.

Good luck!

 

[prometro]

Ok, ok, it works perfekt Thank you! But I did next step and I have other question, I hope the last one to solve this work :

I have this tab (id ; companyName ; value + or -) :
1 ; CompanyA ; +
2 ; CompanyB ; +
3 ; CompanyB ; -
4 ; CompanyC ; +
5 ; CompanyD ; -

I did Query1 which separate all companies which have value (+) and
I did Query2 which separate all companies which have value (-).
Now, I need to do the last Query3 with this result :
Query3 will find all companies, where (+) is included and separate all companies where (-) is included.
So result of the example is : CompanyA and CompanyC.
In simplification is result = Query1 minus Query2.

I know only joining queries but not subtraction
Please, look at it. Thank you
Jiri

 

[vbaInet]

Have a look at this:

http://www.techonthenet.com/sql/exists.php

 

[prometro]

Yes, I think that's it! But, unfortunately It doesnt work it to me, please look at it :

tab(id ; companyName ; value + or - ; etc.)
query0, where source is tab(id, companyName ; value "+" or "-") => i.e. all records
query1, where source is tab(id, companyName ; value = "+")
query2, where source is tab(id, companyName ; value = "-")

my query3 is :
SELECT * FROM query0 WHERE NOT EXISTS (select * from query2);
>> Result is = nothing
SELECT * FROM query0 WHERE EXISTS (select * from query1);
>> Result is = all records without selection.

I must do mistake somewhere ...

 

[vbaInet]

If you look at the examples in the link they ALL have a WHERE part in the subquery SELECT statement. Your subquery must link with the main query and it is that WHERE part that is linking the subquery with the main.

SELECT * FROM query0 WHERE NOT EXISTS (select * from query2 WHERE ...)

Have a look at it again.

Maybe this will help too:

http://allenbrowne.com/subquery-01.html

 

[prometro]

Yes, I did it and it works perfect! Thank you very much for help!

 

[vbaInet]

Grand! You're welcome.