Grouped Query

[Warrio]

Is it possible to create a query that selects from a table the group of
records that are equal to another group?
to make it clearer, the table looks like:

ID Name
1 John
1 Carl
1 Catherine
2 John
2 Carl
3 John
3 Mark
3 Carl


How can I ask to get the ID of the group of records that contain ONLY, i.e..
the names "John, "Mark", "Carl"? so the result would be 3
and I can't use SELECT ID FROM myTable WHERE NAME IN ("John, "Mark",
"Carl"), because the result will show all the IDs where one of the names
appear..

Thanks for any suggestion

 

[Guest]

Add the DISTINCT keyword
SELECT DISTINCT ID FROM myTable WHERE NAME IN
("John, "Mark","Carl");

 

[PC Datasheet]

Will either of these work ---
1. Include both fields in a query and set criteria for ID as 3
2. Create a totals query that includes both fields. In the Totals row, set
Max for ID and Group By for Name

 

[Warrio]

First thanks for your answer
with the Distinct, the result will be 1,2 and 3
but me I want that it displays only 3 because only the group 3 contains all
for the names searched

Thanks again for any suggestion!

 

[Warrio]

Thanks for your answer!
the 1st solution you proposed, I can't know which group contain the three
names searched.. it can be 3 or 4 ...
the 2nd, you look by the total number of names, it wouldn't work because it
shows the the groups that have 3 names, the number will be correct, but the
names won't be those I'm looking for

Thanks again!

 

[Rob Oldfield]

Create an initial query...

SELECT Table1.ID, Table1.Name
FROM Table1
WHERE (((Table1.Name) In ("john","mark","carl")));

....then another query (e.g. Query1) based on that one that counts each ID.
If that equals 3 (or however people are in your list) then that ID contains
all three.

If you then want to get IDs that ONLY include those three people then just
set up another query (Query2) to count number of people (in total) per
group. Set the criteria on that to exclude those IDs with 3 people and then
run a subtract query to exclude any groups in Query2 from Query1.

 

[Warrio]

Bingo!!
That's the most correct answer thanks a lot!! and it logical in addition!