Printer Friendly

Defining null values in Microsoft Access.

A Null represents an unknown or a missing value. It is important to understand that a null value does not mean a zero of a text string of one or more blank spaces.

* Zero (0) can have many different meanings. A zero can represent the balance of your bank account, the amount of a product in stock or the amount of art classes with spaces left on them.

* A text string of one or more blank characters will mean absolutely nothing to most people, however it may be particularly useful to a query language such as SQL. In SQL a blank space is a valid character, as is a string of two blank spaces (' '). They hold the same importance as a string of two letters ('XY'). In figure 1 shown below, a blank represents the fact that Warrington does not appear to be located in any County.

* A zero-length string, two consecutive quotes & no space between ("), is also deemed an value to query languages such as SQL and can be meaningful in certain circumstances. You used databases containing an Employee table, and in this table along with FirstName and LastName fields you will also see a MiddleInitial field; this may often contain a zero-length string due to the fact that an Employee may not have a Middle Initial.

The Value of Nulls

When used for its stated purpose, null can be quite useful as shown in the Clients table in figure 1.Null values seen in the ClientMiddieInitial field and the ClientCounty field represent either missing or unknown values. In order to use a null correctly, you will need to understand why they occur:

Human error is commonly associated with the resultant missing values. An example of this may be consider the record for Henry Thomas in the above table. If you are entering the data for this record and fail to ask the client for the County name they live in the data will then be considered missing and the record will be shown including a null. Once this error is realised however, you can then correct it by consulting the client.

Unknown values can appear for a variety of reasons. An example of this may be that the value for it has not yet been defined. On a student course database, there may be a Categories table that defines courses available; you may add a new course to your list, but as yet there may not be a correct category defined that this course will fit in to. Another example of an unknown value is that it is truly unknown refer back to the example concerning Henry Thomas contained in the Clients table; when entering this record, you have asked him for the County that he lives in, he may not a actually know the county and you also do not know the name of the county in question, then at this moment in time, the value is unknown. You can return to correct this problem when either of you determines the correct county

You may also have a case for a null value if you are working in a database and none of its values has a record. An example of this may be the Employees database and a table that contains an HourlyRate salary field. You may see null records here as the fact is the employee won't be paid a fixed salary hourly rate, therefore one will always be null.

You must also consider that there is a slim difference between "is not applicable" and "does not apply to a previous example, "does not apply" is the correct definition as the value definitely can not apply to other of the fields. As another example, suppose you are working in a database that includes a Pat and that the table includes a field relating to Hair Colour; if you are updating this and a patient does not include the colour of their hair then the field will need to include "not applicable". You could use a Null value in this field although it is recommended to use a true value such as "N/A" or "Not Applicable", making information clearer to the user.

Allowing Nulls in a table will depend on exactly how you are wishing to use the data. Below deals with the negative elements of using Nulls within your database:

The Problem with Nulls

Including null values within your data can have an adverse effect when using this data within any mathematical operations. Any operation that includes a null value will result in a null; this being lot value is unknown then the result of the operation will also be unknown. Below shows how using a null in a calculation will alter the outcome:

(100 X 3) + 10 = 310

(Null X 4) + 10 = Null

(20 X Null) + 100 = Null

(20 X 100) + Null = Null

Using the products table below, you can see the effects that including Nulls will have on any mathematical expressions you have used:

ProductCode ProductName CategoryDescription Price Quantity

001 Word 2002 Bible Books 10

002 Access 2002 Bible Books 29.99 5

003 28" TFT IVC Audio/Visual 995.00

004 Nokia 310 Mobile 65.00 10

005 14" Sanyo Audio/Visual 149.99
 Portable TV

006 128mb Pen Drive 49.00 15

Notice how including nulls in a table will effect mathematical

You can see an example of the problem that null values cause when looking at certain records in this table, the Stock Value field derives its results by using the Price and the QuantityInStock value ['Price]*[QuantityInStock]. You can see that for the first record, ProductCode 001, that where a user entered a value for the Price field (i.e. a Null) then the Stock Value also shows as Null.

This can lead to more severe problems as this may go undetected. Suppose you need to calculate--all items that you currently have In Stock; you will not receive an accurate result as values will not be entered into account.

To ensure that the Stock Value can always be calculated, you must first ensure that the Price and QuantityInStock fields can never contain a NULL value.

A further example of the effects of null values can be seen below:

Null values will also effect aggregate functions that incorporate the values of a given field. Consider any aggregate function, for example a Count function, the result will always contain a null if the field contains a null. The table below shows the results of performing a summary calculation that will count the null occurrences of a category in the Products table.
Category Summary

CategoryDescription Total No. Of Occurrences

Audio/Visual 2
Books 2

Null values will effect the results from an aggregate function

The Total No. Of Occurrences is the result of using the expression Count([CategoryDescription])

The table shows that there are "O" occurrences of the unspecified category, implying that each Product been assigned to a category. This is clearly not the case, as viewing the Products table will show the two products that do not belong in any category at present.

Using all of this information, you should now be able to see that allowing missing or unknown value information can have adverse effects another things that you may wish to do with your data and should be considered in the process of database design. Further informatiom from Microsoft Access Database Solutions OnLine Shop.
Figure 1: The Clients Table

ClientID ClientFirstName ClientMiddieInitial ClientLastName

001 James M Lister
002 Samual Helms
003 Tracy L Sanders
004 Henry Thomas
005 Tirn Lee
006 Elizabeth H Brown

ClientID ClientCity/Town ClientCounty

001 Warrington
002 Walsall West Midland
003 Liverpool Merseyside
004 Sale
005 Scarborough North Yorks
006 Worthing West Sussex
COPYRIGHT 2005 A.P. Publications Ltd.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2005, Gale Group. All rights reserved. Gale Group is a Thomson Corporation Company.

Article Details
Printer friendly Cite/link Email Feedback
Publication:Software World
Geographic Code:1USA
Date:May 1, 2005
Previous Article:The Sarbox conspiracy.
Next Article:Search engine algorithms.

Related Articles
Ask an FEI researcher about... Business Performance Intelligence Software. (Resources).
Business intelligence.
Interactive Intelligence integrates with Microsoft CRM. (Strategic Alliances).
NTT Data Develops Patent Document Analysis System Based on Natural Language Processing Technology.
Code folding--a solution looking for a problem.

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters