Null or NOT in another table

Discussion in 'Microsoft Access Queries' started by atledreier, May 3, 2010.

  1. atledreier

    atledreier Guest

    Hello.

    I have a query that verifies that required fields in my records are
    populated and correct.
    Right now it returns the records that have required fields=NULL. some
    of the fields need to be verified for legal values from another table
    as well. So I need to return values where the value of field [Tag]!
    [function_code] is not in [WM_GOC_Conversion]![Function_Code]

    My current query is a datasheet with autoformat that marks the null-
    fields with yellow background. I'd like to be able to mark the wrong
    information with red text, but first I need to have a query that
    return the records! :)
     
    atledreier, May 3, 2010
    #1
    1. Advertisements

  2. On Mon, 3 May 2010 04:57:56 -0700 (PDT), atledreier
    <> wrote:

    Are those FunctionCode values required to come from the Conversion
    table. or is that optional?

    -Tom.
    Microsoft Access MVP


    >Hello.
    >
    >I have a query that verifies that required fields in my records are
    >populated and correct.
    >Right now it returns the records that have required fields=NULL. some
    >of the fields need to be verified for legal values from another table
    >as well. So I need to return values where the value of field [Tag]!
    >[function_code] is not in [WM_GOC_Conversion]![Function_Code]
    >
    >My current query is a datasheet with autoformat that marks the null-
    >fields with yellow background. I'd like to be able to mark the wrong
    >information with red text, but first I need to have a query that
    >return the records! :)
     
    Tom van Stiphout, May 3, 2010
    #2
    1. Advertisements

  3. atledreier

    vanderghast Guest

    Validations inter table are generally made trough data relation.

    As example, to be sure that a table1.f1 value is not in table2.f2, someone
    MAY come with a design implying a merge (with UNION ALL) of the two tables
    into one, where the new field f3, filled with values from f1 and f2, would
    not allow duplicated values. But that works only if table1 and table2 are
    compatible (or if they share in common a portion of the fields, ie, if they
    have in common a "sub-type").


    With Jet 4.0, you can write more generic table validations rules, BETWEEN
    TABLES, with the CHECK constraint. There is no User Interface, though, so
    you have to use DDL-SQL. As example, to enforce the previous 'rule', someone
    may add the following rule, to table1:

    CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT
    table1f1NotInTable2F2 CHECK(0=(SELECT COUNT(table2.f2) FROM table2 WHERE
    table2.f2 = table1.f1))"

    and, to table2:

    CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRAINT
    table2f2NotInTable1F1 CHECK(0=(SELECT COUNT(table1.f1) FROM table1 WHERE
    table1.f1 = table2.f2))"


    You can drop such a CHECK constraint with (since there is no user interface
    to drop it otherwise) something like :

    CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT
    table1f1NotInTable2F2"

    as example.

    Note that the check constraint name has to be unique, over the whole
    database.
    Note that a check constraint fires when data is appended, or modified, but
    not on deletion. The check is made with values AFTER the tried
    update/insertion is, temporary, done.
    Note that no VBA function must be involved in the CHECK constraint (ex,
    impossible to use Nz( ), while iif( ) is ok ).
    Note that MS SQL Server does not allow this kind of CHECK constraint
    involving other tables (or other rows of the same table); you would a
    trigger, instead.



    Vanderghast, Access MVP


    "atledreier" <> wrote in message
    news:...
    > Hello.
    >
    > I have a query that verifies that required fields in my records are
    > populated and correct.
    > Right now it returns the records that have required fields=NULL. some
    > of the fields need to be verified for legal values from another table
    > as well. So I need to return values where the value of field [Tag]!
    > [function_code] is not in [WM_GOC_Conversion]![Function_Code]
    >
    > My current query is a datasheet with autoformat that marks the null-
    > fields with yellow background. I'd like to be able to mark the wrong
    > information with red text, but first I need to have a query that
    > return the records! :)
     
    vanderghast, May 3, 2010
    #3
  4. atledreier

    atledreier Guest

    Vanderghast: I'm going to use a lookup in a form from now on, i just
    need this to clean up the data already in the table

    Marshal Barton: My original query:

    SELECT Tag.Tag, Tag.Description, Tag.Tag_cat, Tag.Function_code,
    Tag_diverse.Disiplin, Tag.System, Tag_diverse.Comm_Pkg, Tag.Area,
    [Tag_format_in/_br/_te].Fire_area, Tag.Loop
    FROM ([Tag_format_in/_br/_te] INNER JOIN Tag ON [Tag_format_in/_br/
    _te].Tag = Tag.Tag) INNER JOIN Tag_diverse ON Tag.Tag =
    Tag_diverse.Tag
    WHERE (((Tag.Loop) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Description) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Tag_cat) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Function_code) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag_diverse.Disiplin) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.System) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag_diverse.Comm_Pkg) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Area) Is Null) AND ((Tag.Flag) Is Null)) OR ((([Tag_format_in/
    _br/_te].Fire_area) Is Null) AND ((Tag.Flag) Is Null));

    Basically checking one after the other for NULL values, and making
    sure the 'Flag' field is null for each test.

    So in addition I need to check that Function_code is valid from the
    WM_GOC_Conversion.Function_code.

    I also need a few other checks in there, but they may be more
    complex...

    I have a table with fields Area and Fire_area. The user select an area
    in a combobox and a second combobox allow the users to select one of
    the appropriate fire_areas.

    P01 -A1
    P01-A2
    P02 -C1
    P02 -C2
    ....and so on.

    Earlier I had one lookup with all the areas, and one with all the
    fire_areas, and no validation between them. I'd like to check if all
    the fire-areas are legal too.


    On 3 Mai, 16:22, "vanderghast" <vanderghast@com> wrote:
    > Validations inter table are generally made trough data relation.
    >
    > As example, to be sure that a table1.f1 value is not in table2.f2, someone
    > MAY come with a design implying a merge (with UNION ALL) of the two tables
    > into one, where the new field f3, filled with values from f1 and f2, would
    > not allow duplicated values. But that works only if table1 and table2 are
    > compatible (or if they share in  common a portion of the fields, ie, ifthey
    > have in common a "sub-type").
    >
    > With Jet 4.0, you can write more generic table validations rules, BETWEEN
    > TABLES, with the CHECK constraint. There is no User Interface, though, so
    > you have to use DDL-SQL. As example, to enforce the previous 'rule', someone
    > may add the following rule, to table1:
    >
    >     CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT
    > table1f1NotInTable2F2 CHECK(0=(SELECT COUNT(table2.f2) FROM table2 WHERE
    > table2.f2 = table1.f1))"
    >
    > and, to table2:
    >
    >     CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRAINT
    > table2f2NotInTable1F1 CHECK(0=(SELECT COUNT(table1.f1) FROM table1 WHERE
    > table1.f1 = table2.f2))"
    >
    > You can drop such a CHECK constraint with (since there is no user interface
    > to drop it otherwise) something like :
    >
    >     CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT
    > table1f1NotInTable2F2"
    >
    > as example.
    >
    > Note that the check constraint name has to be unique, over the whole
    > database.
    > Note that a check constraint fires when data is appended, or modified, but
    > not on deletion. The check is made with values AFTER the tried
    > update/insertion is, temporary, done.
    > Note that no VBA function must be involved in the CHECK constraint (ex,
    > impossible to use Nz( ), while iif( ) is ok ).
    > Note that MS SQL Server does not allow this kind of CHECK constraint
    > involving other tables (or other rows of the same table); you would a
    > trigger, instead.
    >
    > Vanderghast, Access MVP
    >
    > "atledreier" <> wrote in message
    >
    > news:...
    >
    >
    >


    > > Hello.

    >
    > > I have a query that verifies that required fields in my records are
    > > populated and correct.
    > > Right now it returns the records that have required fields=NULL. some
    > > of the fields need to be verified for legal values from another table
    > > as well. So I need to return values where the value of field [Tag]!
    > > [function_code] is not in  [WM_GOC_Conversion]![Function_Code]

    >
    > > My current query is a datasheet with autoformat that marks the null-
    > > fields with yellow background. I'd like to be able to mark the wrong
    > > information with red text, but first I need to have a query that
    > > return the records! :)
     
    atledreier, May 4, 2010
    #4
  5. atledreier

    vanderghast Guest

    If all the values are in the same record, you can add what is called a table
    validation rule (which is, really, a RECORD validation rule), which *has* a
    user interface, in table design, to specify it. Basically, if you type the
    content of your WHERE clause there, you will then ENFORCE that condition to
    stand for each and every record in the table. It is in the table properties
    sheet (NOT field properties), under Validation Rule, that you would have to
    paste the expression. You can add a custom text, in Validation Text, to
    report error, that is, when the condition is not met while a new record is
    appended, or modified.

    For the second question, it is a matter to define a relation between your
    actual table and the table defining all possible fire area values, and to
    ENFORCE the relation (not necessary to cascade it, for deletion or update,
    but need to be enforced to make sure the value in the field is always one of
    the referred fire area values stored in the reference table). It is under
    Database Tools tab (Access 2007), bring the two tables, drag fire area field
    from the reference table to main table, and edit the relation. Check the box
    "Enforce Referential Integrity", once you are sure the matching fields are
    properly involved by the relation.


    Vanderghast, Access MVP


    "atledreier" <> wrote in message
    news:...
    Vanderghast: I'm going to use a lookup in a form from now on, i just
    need this to clean up the data already in the table

    Marshal Barton: My original query:

    SELECT Tag.Tag, Tag.Description, Tag.Tag_cat, Tag.Function_code,
    Tag_diverse.Disiplin, Tag.System, Tag_diverse.Comm_Pkg, Tag.Area,
    [Tag_format_in/_br/_te].Fire_area, Tag.Loop
    FROM ([Tag_format_in/_br/_te] INNER JOIN Tag ON [Tag_format_in/_br/
    _te].Tag = Tag.Tag) INNER JOIN Tag_diverse ON Tag.Tag =
    Tag_diverse.Tag
    WHERE (((Tag.Loop) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Description) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Tag_cat) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Function_code) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag_diverse.Disiplin) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.System) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag_diverse.Comm_Pkg) Is Null) AND ((Tag.Flag) Is Null)) OR
    (((Tag.Area) Is Null) AND ((Tag.Flag) Is Null)) OR ((([Tag_format_in/
    _br/_te].Fire_area) Is Null) AND ((Tag.Flag) Is Null));

    Basically checking one after the other for NULL values, and making
    sure the 'Flag' field is null for each test.

    So in addition I need to check that Function_code is valid from the
    WM_GOC_Conversion.Function_code.

    I also need a few other checks in there, but they may be more
    complex...

    I have a table with fields Area and Fire_area. The user select an area
    in a combobox and a second combobox allow the users to select one of
    the appropriate fire_areas.

    P01 -A1
    P01-A2
    P02 -C1
    P02 -C2
    ....and so on.

    Earlier I had one lookup with all the areas, and one with all the
    fire_areas, and no validation between them. I'd like to check if all
    the fire-areas are legal too.


    On 3 Mai, 16:22, "vanderghast" <vanderghast@com> wrote:
    > Validations inter table are generally made trough data relation.
    >
    > As example, to be sure that a table1.f1 value is not in table2.f2, someone
    > MAY come with a design implying a merge (with UNION ALL) of the two tables
    > into one, where the new field f3, filled with values from f1 and f2, would
    > not allow duplicated values. But that works only if table1 and table2 are
    > compatible (or if they share in common a portion of the fields, ie, if
    > they
    > have in common a "sub-type").
    >
    > With Jet 4.0, you can write more generic table validations rules, BETWEEN
    > TABLES, with the CHECK constraint. There is no User Interface, though, so
    > you have to use DDL-SQL. As example, to enforce the previous 'rule',
    > someone
    > may add the following rule, to table1:
    >
    > CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT
    > table1f1NotInTable2F2 CHECK(0=(SELECT COUNT(table2.f2) FROM table2 WHERE
    > table2.f2 = table1.f1))"
    >
    > and, to table2:
    >
    > CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRAINT
    > table2f2NotInTable1F1 CHECK(0=(SELECT COUNT(table1.f1) FROM table1 WHERE
    > table1.f1 = table2.f2))"
    >
    > You can drop such a CHECK constraint with (since there is no user
    > interface
    > to drop it otherwise) something like :
    >
    > CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT
    > table1f1NotInTable2F2"
    >
    > as example.
    >
    > Note that the check constraint name has to be unique, over the whole
    > database.
    > Note that a check constraint fires when data is appended, or modified, but
    > not on deletion. The check is made with values AFTER the tried
    > update/insertion is, temporary, done.
    > Note that no VBA function must be involved in the CHECK constraint (ex,
    > impossible to use Nz( ), while iif( ) is ok ).
    > Note that MS SQL Server does not allow this kind of CHECK constraint
    > involving other tables (or other rows of the same table); you would a
    > trigger, instead.
    >
    > Vanderghast, Access MVP
    >
    > "atledreier" <> wrote in message
    >
    > news:...
    >
    >
    >


    > > Hello.

    >
    > > I have a query that verifies that required fields in my records are
    > > populated and correct.
    > > Right now it returns the records that have required fields=NULL. some
    > > of the fields need to be verified for legal values from another table
    > > as well. So I need to return values where the value of field [Tag]!
    > > [function_code] is not in [WM_GOC_Conversion]![Function_Code]

    >
    > > My current query is a datasheet with autoformat that marks the null-
    > > fields with yellow background. I'd like to be able to mark the wrong
    > > information with red text, but first I need to have a query that
    > > return the records! :)
     
    vanderghast, May 4, 2010
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. April

    Using Null and Not Null in IIF Statement

    April, Sep 16, 2003, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    392
    Duane Hookom
    Sep 17, 2003
  2. Amy Johnson

    Counting Null and Not Null values in one query

    Amy Johnson, Nov 20, 2004, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    542
    Steve Schapel
    Nov 20, 2004
  3. Guest

    field Is Null/Is Not Null criterion and IIf function

    Guest, Dec 15, 2004, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    454
    Marshall Barton
    Dec 16, 2004
  4. jer

    null or is not null

    jer, Apr 1, 2005, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    129
    [MVP] S.Clark
    Apr 1, 2005
  5. Guest

    Null Group Fields Not Equal to Null Non-Grouped Fields

    Guest, Apr 28, 2005, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    403
    Ken Snell [MVP]
    Apr 29, 2005
Loading...

Share This Page