Prev: search with multi values and get resaults order
Next: access 2007 use the old query builder format
From: Sue Compelling on 23 Dec 2009 05:28
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
From: Jerry Whittle on 23 Dec 2009 09:49
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
From: John Spencer on 23 Dec 2009 09:52
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: Sue Compelling on 23 Dec 2009 16:08
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?
> >
> .
>
From: Jerry Whittle on 23 Dec 2009 16:55
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?
> > >
> > .
> >
 |  Next  |  Last
Pages: 1 2 3
Prev: search with multi values and get resaults order
Next: access 2007 use the old query builder format