Re: Exclude records that meet criteria

This thread was started in 2004. I have learned a few extra things since then.
In order not to have NULL value mess up your queries (for instance in the
where clause, etc, ...), place the following statements in the body of your
stored procedure or query:
-----------------------------------
set ANSI_NULLS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
-----------------------------------
For more info, do a help and search for the above from within MS SQL.

David
2/27/07




"Christian" wrote:

Hi,
I have tried it, but when I tested, it worked with the "In (SELECT...)" to
include those records, but it didn't work with the "Not in (SELECT...) to
exclude the records.
It took me a while to figure out why.
It is important to mention that if the field used in the subquery has Null
values, those values must be excluded in the subquery criteria, otherwise,
the main query will never return anything.
Something like "Not In (SELECT FIELDNAME FROM Tablename WHERE FIELDNAME Is
Not Null...)

I hope this might help someone else with the same problem.

Kind regards,

"David" wrote:

Hi Nikos,
Thank you for the response.
I have tried and it works.
Regards,
David

"Nikos Yannacopoulos" wrote:

David,

If I understand this right, you want a query which will reurn records with
FirstNames which do not exist in the other table?
If this is the case, use a subquery on the second table in the criteria
under the FirstName field of table A, like:

Not In (SELECT FirstName FROM [Table B])

Just change the table and field names to the actual ones, and it should do
the job.

Likewise, omitting the Not at the beginning would return records with common
first names.

HTH,
Nikos

"David" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB98EDC6-77DF-4C5B-B395-C55881B8BB8E@xxxxxxxxxxxxxxxx
Hi,
In a select querry, records the meet criteria are normally included in the
selection.
However, I need to do just the opposite.
I need to exclude rather than include records from a table that meet
querry
criteria.
Can anyone tell me how this may be accomplished?
Note: The criteria I used simply compares a field in the main table (whose
records I want to prune) with a field in a second table. (e.g. check if
"FirstName" in Table A is the same as "FirstName" in Table B.)
Also, if you suggest a solution that involves Visual Basic coding, I would
appreciate you also provide a sample code that I may modify.
I am new to very new to Visual Basic.
Thanks.
David




.

Relevant Pages

  • Re: Criteria & <>
    ... I'm trying to create a year-end check out report for my school. ... I'm having a problem creating the query for this new report. ... exclude certain records. ... I dropped to the "or" criteria line and added another title ID that I ...
    (microsoft.public.access.queries)
  • Re: Exclude records that meet criteria
    ... "david" wrote: ... It is important to mention that if the field used in the subquery has Null ... use a subquery on the second table in the criteria ... I need to exclude rather than include records from a table that meet ...
    (microsoft.public.access.modulesdaovba)
  • Re: Exclude records that meet criteria
    ... It is important to mention that if the field used in the subquery has Null ... use a subquery on the second table in the criteria ... In a select querry, records the meet criteria are normally included in the ... I need to exclude rather than include records from a table that meet ...
    (microsoft.public.access.modulesdaovba)
  • RE: Exclude
    ... to exclude results based upon date, put in the criteria of your query for ... how can I only exclcude previous weeks report weekly per completed/cancelled ...
    (microsoft.public.access.queries)
  • Re: show the attributes of the selected records
    ... I tried your codes. ... What I want is to exclude the date if there is any bad quality data in that ... but I don't know how to realize it using query. ... Did putting the subquery in the Join run significantly ...
    (microsoft.public.access.queries)