PC Review


Reply
Thread Tools Rate Thread

Null or NOT in another table

 
 
atledreier
Guest
Posts: n/a
 
      3rd May 2010
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! :-)
 
Reply With Quote
 
 
 
 
Tom van Stiphout
Guest
Posts: n/a
 
      3rd May 2010
On Mon, 3 May 2010 04:57:56 -0700 (PDT), atledreier
<(E-Mail Removed)> 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! :-)

 
Reply With Quote
 
 
 
 
vanderghast
Guest
Posts: n/a
 
      3rd May 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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! :-)


 
Reply With Quote
 
atledreier
Guest
Posts: n/a
 
      4th May 2010
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" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>


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


 
Reply With Quote
 
vanderghast
Guest
Posts: n/a
 
      4th May 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>


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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Disallow null/allow null depending on the selection of another fie Flydianslip Microsoft Access VBA Modules 0 18th Mar 2010 08:40 PM
Failed to save table attributes of (null) into (null). Luca Brasi Microsoft Excel Misc 2 4th Feb 2009 05:30 PM
cell value based on null/not null in another cell =?Utf-8?B?c3BlbmNl?= Microsoft Excel Worksheet Functions 1 19th Feb 2006 12:49 AM
Null result when combining null field with non-null field in ADP View Lauren Quantrell Microsoft Access Form Coding 8 17th Nov 2003 03:34 AM
Re: Line20: Error: 'null' is null or not an object Dublevay Windows XP General 0 6th Nov 2003 10:21 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:58 PM.