Build query that returns Parent Values where ALL the child values Ispecify match my criteria

[Andy79]

Dear All,

Here is my simple database:

Table 1 contains "Rooms"
Table 2 contains "Tasks"

Table 1 is linked to table 2 by a 1-to-many link. So that every Room
has many tasks.

I want a query to return the Rooms where ALL the tasks are the same as
the criteria I define.

This is as far as I got, but this query below, returns all the rooms
that have the criteria at least once - not the room that have ONLY
that criteria..

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1"));


Help please!!!

Many Thanks
Andy

 

[Ken Snell \(MVP\)]

Try this:

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1")
AND DCount("*", "Tasks", "[Task_name]='Example1' AND
[Room_ID]=" & Room.Room_ID) = 1);
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Andy79]

Thanks Ken,

This is a step in the right direction, however this query only returns
all the rooms that have exactly 1 instance of "example1" as a task.
What I am looking for is a query that returns the rooms whose tasks
are all "example1". Rooms have anywhere between 3 tasks and 20
tasks...

thanks again for the help

Regards
Andy

Try this:

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1")
AND DCount("*", "Tasks", "[Task_name]='Example1' AND
[Room_ID]=" & Room.Room_ID) = 1);
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Dear All,

Here is my simple database:

Table 1 contains "Rooms"
Table 2 contains "Tasks"

Table 1 is linked to table 2 by a 1-to-many link. So that every Room
has many tasks.

I want a query to return the Rooms where ALL the tasks are the same as
the criteria I define.

This is as far as I got, but this query below, returns all the rooms
that have the criteria at least once - not the room that have ONLY
that criteria..

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1"));

Help please!!!

Many Thanks
Andy

 

[Ken Snell \(MVP\)]

OK, then this may be your solution:

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE [Tasks].[Task_name]="Example1"
AND
(SELECT Count(*)
FROM [Tasks] T
WHERE T.[Room_ID]= Room.Room_ID) =
(SELECT Count(*)
FROM [Tasks] TT
WHERE TT.[Room_ID]= Room.Room_ID
AND TT.[Task_name]="Example1");

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks Ken,

This is a step in the right direction, however this query only returns
all the rooms that have exactly 1 instance of "example1" as a task.
What I am looking for is a query that returns the rooms whose tasks
are all "example1". Rooms have anywhere between 3 tasks and 20
tasks...

thanks again for the help

Regards
Andy

Try this:

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1")
AND DCount("*", "Tasks", "[Task_name]='Example1' AND
[Room_ID]=" & Room.Room_ID) = 1);
--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Dear All,

Here is my simple database:

Table 1 contains "Rooms"
Table 2 contains "Tasks"

Table 1 is linked to table 2 by a 1-to-many link. So that every Room
has many tasks.

I want a query to return the Rooms where ALL the tasks are the same as
the criteria I define.

This is as far as I got, but this query below, returns all the rooms
that have the criteria at least once - not the room that have ONLY
that criteria..

SELECT DISTINCT Room.Room_name, [Tasks].[task_name]
FROM Room INNER JOIN [Tasks] ON Room.Room_ID = [Tasks].Room_ID
WHERE ((([Tasks].[Task_name])="Example1"));

Help please!!!

Many Thanks
Andy

 

[John Spencer]

Sounds as if the following should work as long as your data set is not
too large.

SELECT ROOMS.*
FROM Rooms
WHERE Rooms.Room_ID NOT IN
(SELECT Tasks.Room_ID
FROM TASKS
WHERE Tasks.TaskName <> "Example1"
AND Room_ID is not Null)

More complex, but probably faster.

SELECT Rooms.*
FROM Rooms LEFT JOIN
(
SELECT Tasks.Room_ID
FROM TASKS
WHERE Tasks.TaskName <> "Example1"
) As X
ON Rooms.Room_ID = X.Room_ID
WHERE X.Room_ID is NULL


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Andy79]

Many thanks for the help..

In the end I went with:

SELECT distinct Conferences.Conference_Title, [CR Commissioning].[CM
Status]
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE ((([CR Commissioning].[CM Status])="dropped") AND
((Conferences.Conference_ID) Not In (SELECT [CR
Commissioning].Conference_ID
from [CR Commissioning]
WHERE [CR Commissioning].[CM Status] <> "dropped"
AND Conference_ID IS NOT Null)));

regards
Andy