Where Not Exists

[Sue Compelling]

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?

 

[Jerry Whittle]

Please post the entire SQL for both the NOT IN and NOT EXISTS queries.
Sometimes EXISTS require a join statement to work right.

 

[John Spencer]

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]

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

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

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?

.

 

[Jerry Whittle]

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.

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

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

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?

.

 

[PieterLinden via AccessMonster.com]

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.

 

[Sue Compelling]

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

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

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?

.

 

[John Spencer]

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]

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

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

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]

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

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

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

.

 

[John Spencer]

Great. Glad you got this to work.

There is usually more than one way to get the desired results.

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

 

[PieterLinden via AccessMonster.com]

Great. Glad you got this to work.

There is usually more than one way to get the desired results.

FWIW, I try to NEVER use IN/NOT IN with large datasets. Performance is
*awful*. You're much better off learning how to do outer joins and using
indexed columns in the join.

SELECT *
FROM tableA LEFT JOIN tableB ON tableA.PrimaryKeyField = tableB.
ForeignKeyField
WHERE tableB.ForeignKeyField IS NULL