Prev: search with multi values and get resaults order
Next: access 2007 use the old query builder format
From: PieterLinden via AccessMonster.com on 23 Dec 2009 17:53
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/Forums.aspx/access-queries/200912/1

From: Sue Compelling on 23 Dec 2009 21:50
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?
> >
> .
>
From: John Spencer on 24 Dec 2009 08:38
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
From: Sue Compelling on 26 Dec 2009 01:24
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
> .
>
From: Sue Compelling on 26 Dec 2009 01:54
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
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: search with multi values and get resaults order
Next: access 2007 use the old query builder format