[AccessD] Outputing nulls in query with function

And to which I reply ... hooey.

The absence of a value doesn't in fact mean anything.  It certainly does not
mean you don't know the answer.  It doesn't even mean the answer was never
entered.  It MIGHT mean that, with the enforcement of a bunch of rules but
it certainly doesn't inherently mean that.  And even if it does mean that,
it may or may not be any more valuable than a default value.  Furthermore a
default value can explicitly mean that (unknown).  I can have a table of
colors (for example) where the value zero is called "unknown", the value 1
is called black, 2 is brown etc.  

So IMNSHO, the value of NULL is way over simplified in such discussions.  

Let's take Arthur's example since he brought it up.  A null does NOT mean
you don't know the number of children.  What is he advising you to do?
Insert nulls with a query to replace the zeros already in place (with a
perfectly proper caution).  So what do those nulls now mean?  They mean you
ran a query and inserted nulls in the field.  Do you in fact know that a
zero means "unknown"?  Obviously not since the zero could (at this point in
time) mean unknown or in fact it could mean zero.  You MAY HAVE overwritten
valid zeros (they have no children) with a null and so now you are saying
you don't know how many children when in fact you did (in some cases).  Why
not make -1 the default value and specifically have -1 mean "unknown"?  Why
would I do that?  We will get to that in a minute.

So it becomes quite obvious to the most casual observer that, if any old
hooligan can insert a NULL in a field with a valid value (a zero in this
case) then the null means nothing more than some hooligan put a null in
there.  Except... oops, it doesn't even mean that since NOW you are going to
have "hooligan nulls" intermixed with real "never entered" nulls.

>From THIS discussion it becomes obvious that IF you start from the VERY
BEGINNING with a  table with a default value of null, and IF you prevent
hooligans from erasing valid values and placing nulls in there, THEN the
null might mean unknown.  It also might mean that the (valid known) answer
wasn't entered of course.  So now you have the null meaning "never entered"
and that (in the end) does NOT equate to "unknown", it really just means
"never entered".

NOW for the down side... NULLS wreak havoc with all kinds of operations.
Inner joins for example.  Math operations for another example.  Some types
of string operations for another example.  So... for all of your efforts to
get back to using nulls, you can (and probably will) BREAK THINGS in your
application.

Let me tell you a real life story.  I was brought in to maintain a database
where the original data was so poorly entered (and in fact designed) that
they could not get accurate results.  However because they did in fact use
nulls, EVERY SINGLE FIELD in the database had nulls in it, including all of
the foreign key fields.  Which meant EVERY SINGLE query in the database, and
EVERY SINGLE JOIN in the database had to use outer joins.  Inner joins
simply did not work.  Furthermore, because of the way access treats outer
joins, there was no way I could begin to use inner joins since I would get
the old "ambiguous join" message as soon as I tried to use an inner join in
amongst all the existing outer joins.  

Have you ever worked in a database where you open a query and every join is
an outer join?  Now you know why.  

Inner joins serve a very useful purpose but they were virtually unusable in
any complex query because of the requirement to pull in nulls (outer join)
if there was not a "valid" value.  It is of course perfectly possible to
have a record in the table which SPECIFICALLY MEANS "unknown", in which case
you suddenly have a "valid value" for the "unknown" and you can use inner
joins wherever they are appropriate.

I just thought I should pour a little cold water of reality on the purist
hooey.  So use Nulls if you must, but beware the pitfalls, and be aware of
the alternatives.

And of course we have been over all of this before and so now the flames
begin.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, November 18, 2007 12:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Outputing nulls in query with function

First step would be to ensure that all values in the column are nulls when
they are not meaningful (although as Susan pointed out, null is meaningful).
What I mean is that zero is not equivalent to null.

You can do this at the table-design level by changing the properties to
"allow null = yes" and then erase the default value property, which tends to
go to zero, and that's not what you want. So once you have eliminated the
zeroes and replaced them will nulls, then you're in business, and the front
end won't let you deposit zeroes.

Even that oversimplifies, and sorry about that. Suppose, for example, the
column was called NumberOfChildren. The answer, as in my case, is zero. That
is a valid answer, and different from null, which indicates the absence of
information, not its presence. Retrofitting this analysis to existing data
may be non-trivial. If you don't know how many kids I have, then the
appropriate value is Null. If you do know that I have no kids, then the
appropriate value is zero. Either way, you have to resolve the existing data
before you can enforce it at the table-level. I hope that you don't have a
lot of data already recorded with this ambiguity. If you do, then you don't
have much choice but to check all the questionable values (does zero mean no
kids or we don't know?). That's not going to be easy, but sometimes accuracy
outweighs easiness.

hth,
Arthur

On 11/18/07, Bob Heygood <bheygood at abestsystems.com> wrote:
>
> Yes, we need to do things like count and such and use TAStats.
>
> Thanks for replying.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan 
> Harkins
> Sent: Sunday, November 18, 2007 8:34 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Outputing nulls in query with function
>
> > I have a query that outputs a column that uses a user function to 
> > create it.
> > Works great.
> > The client wants the results in that column to either be a value or 
> > null, not an empty string or zero. We are going to do some 
> > statistical jive later down the road.
> > So, how can I output a null?
>
> =======Null has a specific meaning -- forcing it might usurp that, are 
> you sure you really want to do that?
>
> Susan H.