Null or NOT in another table

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! :-)

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! :-)

atledreier wrote:
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! :-)


That sounds like what the unmatched query wizard would
translate into an outer join. Without seeing your original
query, all I can say is that you might be able to get that
data from a query something like:

SELECT Tag.*, WM_GOC_Conversion.Function_Code As Test
FROM Tag LEFT JOIN WM_GOC_Conversion
ON Tag.function_code = WM_GOC_Conversion.Function_Code
WHERE Tag,f1 Is Null
OR Tag,f2 Is Null
. . .
OR WM_GOC_Conversion.Function_Code

To display that the way you want, you need to use a form
where you can utilize Conditonal Formatting to get the
colors. For the missing value fields, use the condition Is
Null and set the BackColor to yellow. For the function code
field, use the Expression Is: option with the expression
[Test] Is Null and set the ForeColor to Red

--
Marsh
MVP [MS Access]

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
...
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: 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

...




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! :-)

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
...
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

...




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! :-)