Unmatch, but exist in both tables

  • Thread starter Thread starter Steen
  • Start date Start date
S

Steen

Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
 
Try: (not tested)

SELECT [Name]
FROM [Prod]
WHERE Not EXISTS
(
SELECT [id_prod]
FROM [Part]
WHERE ([Part].[id_prod] = [Prod].[id_part])
AND ([Part].[Volyme] BETWEEN 400 AND 700)
)

There may be other more effcient SQL Strings.
 
Thank You very much.
It works perfect.

/Steen


Van T. Dinh said:
Try: (not tested)

SELECT [Name]
FROM [Prod]
WHERE Not EXISTS
(
SELECT [id_prod]
FROM [Part]
WHERE ([Part].[id_prod] = [Prod].[id_part])
AND ([Part].[Volyme] BETWEEN 400 AND 700)
)

There may be other more effcient SQL Strings.

--
HTH
Van T. Dinh
MVP (Access)


Steen said:
Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
Click to expand...
Click to expand...
 
Hi Steen,

The following is just another way
(once demonstrated by Michel for situation
to find who has not taken a course)

SELECT Prod.[Name]
FROM Prod INNER JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.[Name]
HAVING
(((Min([Volyme] Between 400 And 700))=0));

Basically you have a "test condition", in this case
for each group of [Name],
is "[Volyme] Between 400 And 700"?

If that condition is never met over the group,
the min of that true/false condition will be 0 (false)
and that group will be returned by the query.

If that condition is ever met within the group,
the min of that true/false condition will be -1 (true)
and that group will not be returned by the query.

Pretty clever.

Just another way....

BTW, "Name" is a reserved word in Access
and you should probably change the field name
to something else (unless this was just a simple
example of your structure).

Good luck,

Gary Walter
 
Hi Gary

Without testing, my guess is that the proposed SQL String won't returns
"Nils" as requested by Steen.

--
HTH
Van T. Dinh
MVP (Access)



Gary Walter said:
Hi Steen,

The following is just another way
(once demonstrated by Michel for situation
to find who has not taken a course)

SELECT Prod.[Name]
FROM Prod INNER JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.[Name]
HAVING
(((Min([Volyme] Between 400 And 700))=0));

Basically you have a "test condition", in this case
for each group of [Name],
is "[Volyme] Between 400 And 700"?

If that condition is never met over the group,
the min of that true/false condition will be 0 (false)
and that group will be returned by the query.

If that condition is ever met within the group,
the min of that true/false condition will be -1 (true)
and that group will not be returned by the query.

Pretty clever.

Just another way....

BTW, "Name" is a reserved word in Access
and you should probably change the field name
to something else (unless this was just a simple
example of your structure).

Good luck,

Gary Walter


Steen said:
Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
Click to expand...
Click to expand...
 
Thanks Van,

One of the most common programming
errors --

forgetting to handle the "Nils" case.8-)

So...tested, no guess:

SELECT Prod.EmpName
FROM Prod LEFT JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.EmpName
HAVING
(((Min(Nz([Volyme],0)
Between 400 And 700))=0));

Gary Walter

Van T. Dinh said:
Hi Gary

Without testing, my guess is that the proposed SQL String won't returns
"Nils" as requested by Steen.

--
HTH
Van T. Dinh
MVP (Access)



Gary Walter said:
Hi Steen,

The following is just another way
(once demonstrated by Michel for situation
to find who has not taken a course)

SELECT Prod.[Name]
FROM Prod INNER JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.[Name]
HAVING
(((Min([Volyme] Between 400 And 700))=0));

Basically you have a "test condition", in this case
for each group of [Name],
is "[Volyme] Between 400 And 700"?

If that condition is never met over the group,
the min of that true/false condition will be 0 (false)
and that group will be returned by the query.

If that condition is ever met within the group,
the min of that true/false condition will be -1 (true)
and that group will not be returned by the query.

Pretty clever.

Just another way....

BTW, "Name" is a reserved word in Access
and you should probably change the field name
to something else (unless this was just a simple
example of your structure).

Good luck,

Gary Walter


Steen said:
Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
Click to expand...
Click to expand...
Click to expand...
 
Maybe even faster:

SELECT Prod.EmpName
FROM Prod LEFT JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.EmpName
HAVING
(((Min([Volyme] Between 400 And 700))=0))
OR
(((Min(Part.Volyme)) Is Null));

Gary Walter said:
Thanks Van,

One of the most common programming
errors --

forgetting to handle the "Nils" case.8-)

So...tested, no guess:

SELECT Prod.EmpName
FROM Prod LEFT JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.EmpName
HAVING
(((Min(Nz([Volyme],0)
Between 400 And 700))=0));

Gary Walter

Van T. Dinh said:
Hi Gary

Without testing, my guess is that the proposed SQL String won't returns
"Nils" as requested by Steen.

--
HTH
Van T. Dinh
MVP (Access)



Gary Walter said:
Hi Steen,

The following is just another way
(once demonstrated by Michel for situation
to find who has not taken a course)

SELECT Prod.[Name]
FROM Prod INNER JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.[Name]
HAVING
(((Min([Volyme] Between 400 And 700))=0));

Basically you have a "test condition", in this case
for each group of [Name],
is "[Volyme] Between 400 And 700"?

If that condition is never met over the group,
the min of that true/false condition will be 0 (false)
and that group will be returned by the query.

If that condition is ever met within the group,
the min of that true/false condition will be -1 (true)
and that group will not be returned by the query.

Pretty clever.

Just another way....

BTW, "Name" is a reserved word in Access
and you should probably change the field name
to something else (unless this was just a simple
example of your structure).

Good luck,

Gary Walter


Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
Click to expand...
Click to expand...
Click to expand...
 
Thank You all for Your exellent help.
(And I didnt know that "Name" was reserved. I my original table the field
is namn)
/Steen


Gary Walter said:
Maybe even faster:

SELECT Prod.EmpName
FROM Prod LEFT JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.EmpName
HAVING
(((Min([Volyme] Between 400 And 700))=0))
OR
(((Min(Part.Volyme)) Is Null));

Gary Walter said:
Thanks Van,

One of the most common programming
errors --

forgetting to handle the "Nils" case.8-)

So...tested, no guess:

SELECT Prod.EmpName
FROM Prod LEFT JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.EmpName
HAVING
(((Min(Nz([Volyme],0)
Between 400 And 700))=0));

Gary Walter

Van T. Dinh said:
Hi Gary

Without testing, my guess is that the proposed SQL String won't returns
"Nils" as requested by Steen.

--
HTH
Van T. Dinh
MVP (Access)



Hi Steen,

The following is just another way
(once demonstrated by Michel for situation
to find who has not taken a course)

SELECT Prod.[Name]
FROM Prod INNER JOIN Part
ON Prod.id_part = Part.id_prod
GROUP BY Prod.[Name]
HAVING
(((Min([Volyme] Between 400 And 700))=0));

Basically you have a "test condition", in this case
for each group of [Name],
is "[Volyme] Between 400 And 700"?

If that condition is never met over the group,
the min of that true/false condition will be 0 (false)
and that group will be returned by the query.

If that condition is ever met within the group,
the min of that true/false condition will be -1 (true)
and that group will not be returned by the query.

Pretty clever.

Just another way....

BTW, "Name" is a reserved word in Access
and you should probably change the field name
to something else (unless this was just a simple
example of your structure).

Good luck,

Gary Walter


Hi.
I got to 2 tables:
Prod Part
Name id_part id_prod Volyme
John 1 1 200
Nils 2 1 300
Ola 3 3 100
Fredrik 4 3 600
3 900
4 500

I need a query that give the result of who doesnt have a volyme that is
between 400 and 700
John
Nils

Thanks
/Steen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top