MS SQL - impossible to solve a simple problem ???? 3

[src23]

I got myself stuck onto a very strange problem. Am I dumb, or there is no way to solve it ??? Cause it's diving me MAD.

I have simplified the problem:

Suppose I have these two tables:

tblPers
__________________
| IDPers | Name |
|--------|---------
| 1 | AAAA |
| 2 | BBBB |
| 3 | CCCC |
| 4 | DDDD |
| 5 | EEEE |
------------------


tblSales
__________________
| IDPers | IDProd |
|--------|---------|
| 1 | P1 |
| 1 | P2 |
| 1 | P3 |
| 1 | P4 |
|--------+---------|
| 2 | P1 |
| 2 | P3 |
| 2 | P4 |
|--------+---------|
| 3 | P1 |
| 3 | P2 |
| 3 | P3 |
| 3 | P5 |
|--------+---------|
| 4 | P1 |
| 4 | P2 |
| 4 | P4 |
| 4 | P6 |
------------------

The persons in tblPers are selling products. In tblSales are the products sold by each person. I guess it's quite clear.

MY PROBLEM IS: I just wanna get the persons who sold the products P1 AND P2 AND P3 (or ANY OTHER list of products).

The firs query I tried was:

SELECT DISTINCTROW tblPers.IDPers, tblPers.Name
FROM tblPers INNER JOIN tblSales ON tblPers.IDPers = tblSales.IDPers
WHERE tblSales.Product In ("P1","P2","P3");

The result was predictible, and NOT correct:
__________________
| IDPers | Name |
|--------|---------
| 1 | AAAA |
| 2 | BBBB |
| 3 | CCCC |
| 4 | DDDD |
------------------
because only the persons 1 and 3 match the criteria (2 and 4 didn't sold P1 AND P2 AND P3 - as you can see if you look back in tblSales)


Then, I tried a lot of other combinations with imbricated queries.... with no result

Example:
SELECT tblPers.IDPers FROM tblPers WHERE tblPers.IDPers in
(SELECT IDPers FROM tblSales WHERE tblSales.IDProd In ("P1","P2","P3"))
The result was the same....... all the persons were returned...

As I already said, the problem seem so simple, but I can't figure it out.

Using VBA I solved it, with a few LOOPS through some recordsets.

But I'm too damn stubborn, and I wanna solve it ONLY using SQL. And there's gotta be a way to solve it in ONLY ONE query.

Now I realized that, in order to solve the problem, I gotta define a criteria that should NOT check the existance of a SINGLE product in a list of products; my criteria should check if a WHOLE list of products (here - P1 and P2 and P3) is contained in another list of products, grouped on some field (here - the products grouped by each person, in tblSales ).

All my attempts using imbricated SQL did not satisfy this condition. I tried to use ALL, ANY, SOME, EXISTS.... but I reached nowhere.....

I know that somehow I gotta group the products by every person, and then I must compare my list of products with the grouped ones.

I still think there is something that I'm missing.... that I can't see the forest because of the trees.
But there is also a dark thought in my mind: that IT IS NOT POSSIBLE TO SOLVE THIS QUERY WITH ACCESS SQL !!!!


I usually solve by myself any of my problems, no matter how long it would take.
But I see no solution in this case.
Is there anyone who could give me an idea ?


Thanx in advance..

 

[dhookom]

Create a table of unique products

tblProds
===========
IDProd
P1
P2
P3
...

and then try this sql
SELECT tblPers.IDPers, tblPers.PersName
FROM tblPers
WHERE ((((SELECT Count(*) FROM tblSales s where S.IDPers = tblPers.IDPers AND s.IDProd AND Instr( [Enter Products],IDProd)>0))=(SELECT Count(*) FROM tblProds where Instr( [Enter Products],IDProd)>0)));

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[src23]

Man... it's really workin !!! I can't believe it !!!

I've been working with Access for about two years, made a lot of apps... and it never happened to find a problem I couldn't solve......... except this one....

But now I gotta admit... U're the boss, man !!! It's the SMARTEST, the MOST ORIGINAL Solution I've seen so far !!!!
I would've never thought about it !

To be honest, I also gotta admit that... I did't quite understand all what you did there.. except THAT IT'S WORKING !

It's about the syntax here:

SELECT Count(*) FROM tblSales s where S.IDPers = tblPers.IDPers AND s.IDProd AND Instr([Enter Products],IDProd)>0)

How comes "where S.IDPers = tblPers.IDPers AND s.IDProd AND Instr(bla-bla)>0" ??

Anyway... thanx, man ! You helped me a lot !!!

 

[JonFer]

A variation is to add a checkbox to the Unique product list table and then check those that you are interested in.

In the query, combine this table and the sales table on the product ID, group by person, and add a criteria:

Where Count(*)=(Select Count(*) from Products Where Checked)

 

[dhookom]

I appreciate JonFer's solution (actually any solution that doesn't rely on keying in parameters). JonFer's solution could be modified a little to get rid of the Instr() entirely so it would be more generic SQL.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

And what about this ?
SELECT P.IDPers, P.Name FROM tblPers P INNER JOIN
(SELECT IDPers FROM tblSales WHERE Product In ("P1","P2","P3")
GROUP BY IDPers HAVING Count(*)=3) S
ON P.IDPers = S.IDPers;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

PHV,
Nice solution if you know or can use code to build the desired product list.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[src23]

Thanks to all of you for helping me, your solutions were very useful.

My problem is a little bit different from the example here, but based upon your solutions, I figured out a way to implement it.

My database is used to choose persons who meet some criteria. I have an unique persons table, a table of unique criterias (there are four categs of criteria), and a PERSON_CRITERIA table. Each person can meet none, one or more criteria of every category.
I want to find the persons who meet, let's say, crit A1, A3, A4 from categ A, and crit. B2, B5, B6 from categ B.. and so on...........

I used VBA + recordsets, and checkboxes, and it worked OK.

Your help was much apreciated.