Where Not Exists

Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

--
Sue Compelling

Please post the entire SQL for both the NOT IN and NOT EXISTS queries.
Sometimes EXISTS require a join statement to work right.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Sue Compelling" wrote:

Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

--
Sue Compelling

It doesn't work. Does this mean you don't get any records returned or does it
mean something else.

Since you did not restrict the sub-query to returning specific records with a
where clause, EXISTS is always going to be true as long as there is at least
one record returned by qryvolsyrshelping2009.

You could try (as an experiment)
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE 1=2)

That should return all records since Not exists will always be true. If you
want help constructing the proper sub-query for Exists to check, post your
original Not In query. The entire query not just the WHERE clause.

I suspect that you might want
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE ContactID =
[YourTableInMainQuery].ContactID)

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

Sue Compelling wrote:
Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

Hi John and Jerry

Full query below:

SELECT tblContacts.ContactID, tblContacts.OrgTitle, tblContacts.Firstname,
tblContacts.Lastname, tblContacts.[2009], tblContacts.ContactType
FROM tblContacts
WHERE (((tblContacts.ContactID) Not In (select ContactID from
[qryvolsyrshelping2009])) AND ((tblContacts.[2009])=Yes));

Thanks
--
Sue Compelling


"John Spencer" wrote:

It doesn't work. Does this mean you don't get any records returned or does it
mean something else.

Since you did not restrict the sub-query to returning specific records with a
where clause, EXISTS is always going to be true as long as there is at least
one record returned by qryvolsyrshelping2009.

You could try (as an experiment)
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE 1=2)

That should return all records since Not exists will always be true. If you
want help constructing the proper sub-query for Exists to check, post your
original Not In query. The entire query not just the WHERE clause.

I suspect that you might want
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE ContactID =
[YourTableInMainQuery].ContactID)

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

Sue Compelling wrote:
Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

.

1. Does qryvolsyrshelping2009 run by itself and return the expected data?

2. Try to simplify the query and see how it runs. Here's three things to try:

SELECT tblContacts.ContactID,
tblContacts.OrgTitle,
tblContacts.Firstname,
tblContacts.Lastname,
tblContacts.[2009],
tblContacts.ContactType
FROM tblContacts ;

SELECT tblContacts.ContactID,
tblContacts.OrgTitle,
tblContacts.Firstname,
tblContacts.Lastname,
tblContacts.[2009],
tblContacts.ContactType
FROM tblContacts
WHERE tblContacts.[2009]=Yes;


SELECT tblContacts.ContactID,
tblContacts.OrgTitle,
tblContacts.Firstname,
tblContacts.Lastname,
tblContacts.[2009],
tblContacts.ContactType
FROM tblContacts
WHERE tblContacts.ContactID) Not In
(select ContactID
from [qryvolsyrshelping2009]) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Sue Compelling" wrote:

Hi John and Jerry

Full query below:

SELECT tblContacts.ContactID, tblContacts.OrgTitle, tblContacts.Firstname,
tblContacts.Lastname, tblContacts.[2009], tblContacts.ContactType
FROM tblContacts
WHERE (((tblContacts.ContactID) Not In (select ContactID from
[qryvolsyrshelping2009])) AND ((tblContacts.[2009])=Yes));

Thanks
--
Sue Compelling


"John Spencer" wrote:

It doesn't work. Does this mean you don't get any records returned or does it
mean something else.

Since you did not restrict the sub-query to returning specific records with a
where clause, EXISTS is always going to be true as long as there is at least
one record returned by qryvolsyrshelping2009.

You could try (as an experiment)
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE 1=2)

That should return all records since Not exists will always be true. If you
want help constructing the proper sub-query for Exists to check, post your
original Not In query. The entire query not just the WHERE clause.

I suspect that you might want
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE ContactID =
[YourTableInMainQuery].ContactID)

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

Sue Compelling wrote:
Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

.

Sue Compelling wrote:
Hi John and Jerry

Full query below:

SELECT tblContacts.ContactID, tblContacts.OrgTitle, tblContacts.Firstname,
tblContacts.Lastname, tblContacts.[2009], tblContacts.ContactType
FROM tblContacts
WHERE (((tblContacts.ContactID) Not In (select ContactID from
[qryvolsyrshelping2009])) AND ((tblContacts.[2009])=Yes));

Thanks
It doesn't work. Does this mean you don't get any records returned or does it
mean something else.
[quoted text clipped - 34 lines]

.
I try to never use NOT IN... you'd be better off using a LEFT JOIN -
especially if you can use an indexed column in the join.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Hi John

I did the inner query though this still returns the wrong record set (ie -
it returns every record [4,031] in QryVolsYrsHelping2009)

The record set should only be 354 records

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE ((Not (tblContacts.ContactID)=Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)));

Cheers
--
Sue Compelling


"John Spencer" wrote:

It doesn't work. Does this mean you don't get any records returned or does it
mean something else.

Since you did not restrict the sub-query to returning specific records with a
where clause, EXISTS is always going to be true as long as there is at least
one record returned by qryvolsyrshelping2009.

You could try (as an experiment)
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE 1=2)

That should return all records since Not exists will always be true. If you
want help constructing the proper sub-query for Exists to check, post your
original Not In query. The entire query not just the WHERE clause.

I suspect that you might want
Not Exists(select ContactID from [qryvolsyrshelping2009] WHERE ContactID =
[YourTableInMainQuery].ContactID)

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

Sue Compelling wrote:
Hi

Following on from Sunil's query - I had a similar time lag on my query below
- which works but takes forever (only across 7,000 records though) and tried
to change the criteria from:

Not In (select ContactID from [qryvolsyrshelping2009])

to:

Not Exists (select ContactID from [qryvolsyrshelping2009])

though it now doesn't work? Thoughts?

.

Your where clause is incorrect. Try this version.

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE Not Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)
AND tblContacts.[2009]=True


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

Sue Compelling wrote:
Hi John

I did the inner query though this still returns the wrong record set (ie -
it returns every record [4,031] in QryVolsYrsHelping2009)

The record set should only be 354 records

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE ((Not (tblContacts.ContactID)=Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)));

Cheers

Merry Xmas to you John

Unfortunately this query came back with nil records.

I really want to be able to crack this as I have used Not In a number of
times (and it is always slow) and would love to change these expressions to
the Not Exists. I hope you're happy to persevere.

Cheers
--
Sue Compelling


"John Spencer" wrote:

Your where clause is incorrect. Try this version.

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE Not Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)
AND tblContacts.[2009]=True


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

Sue Compelling wrote:
Hi John

I did the inner query though this still returns the wrong record set (ie -
it returns every record [4,031] in QryVolsYrsHelping2009)

The record set should only be 354 records

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE ((Not (tblContacts.ContactID)=Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)));

Cheers
.

Hi John

Thanks for your efforts - I managed to find a similar post and John Vinson
recommended the Query Wizard - "Unmatched Records" - this was perfect for
what I wanted. I couldn't get the right join to work when I was doing it
myself because I wasn't putting null in the criteria for the "not wanted
records".

Cheers
--
Sue Compelling


"John Spencer" wrote:

Your where clause is incorrect. Try this version.

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE Not Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)
AND tblContacts.[2009]=True


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

Sue Compelling wrote:
Hi John

I did the inner query though this still returns the wrong record set (ie -
it returns every record [4,031] in QryVolsYrsHelping2009)

The record set should only be 354 records

SELECT tblContacts.ContactID
FROM tblContacts INNER JOIN QryVolsYrsHelping2009 ON tblContacts.ContactID =
QryVolsYrsHelping2009.ContactID
WHERE ((Not (tblContacts.ContactID)=Exists (select ContactID from
[qryvolsyrshelping2009] WHERE ContactID = [tblContacts].ContactID)));

Cheers
.