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:
Products ProductCode ProductName CategoryDescription Price Quantity InStock 001 Word 2002 Bible Books 10 002 Access 2002 Bible Books 29.99 5 003 28" TFT IVC Audio/Visual 995.00 Television 004 Nokia 310 Mobile 65.00 10 Phone 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 operations
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 0 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
 Printer friendly
 Cite/link
 Email
 Feedback
| |
| Title Annotation: | SOFTWARE INTELLIGENCE |
|---|---|
| Publication: | Software World |
| Geographic Code: | 1USA |
| Date: | May 1, 2005 |
| Words: | 1295 |
| Previous Article: | The Sarbox conspiracy. |
| Next Article: | Search engine algorithms. |
| Topics: | |