Hi,



Real basic question about null values. My agency uses this State supplied 

Web application to store client information. Data entry is done through a 

single main form. I've noticed that the designers don't allow any of the 

fields on the form to contain null values before saving the record. We 

must enter either a value, or a code for "not applicable" or a code 

for "no information". Most of the fields in question contain text values. 

So I'm wondering, from an Access application design best practices point 

of few: should one adopt a strategy such as this to avoid null values in 

record fields at all costs? In the Access apps I've designed, I've allowed 

records to contain null fields if that field is irrelevant for a 

particular client. I end up using outer joins in queries a lot though as a 

result. I don't know if this is good or bad or indifferent. Should every 

field in a saved record contain a value, even if it's just an empty 

string, or a 0, or a code for "not applicable" or "no information"?  

I would say that it depends on the use of the collected data.



I work for a government organisation that collects a great deal of data 

for statistical analysis. A null value could not provide a great deal of 

information to us.



We could not tell whether the data was null because:



a) the user did not want to fill in a particular question

b) that question was not applicable to person filling-in the questionnaire 



c) the user did not know the answer

d) many other variables which could influence the outcome of a particular 

survey.



For statistical projects we usually force users to answer all appropriate 

questions by providing appropriate additional response codes, such as 

"Unknown", "Not Applicable", "Don't wish to answer", ets. The program 

defaults to "No answer given" code of zero(0) if none of the responses 

have been selected. With this method we can actually tell whether the 

person answered our question, whether the person jumped over an inappropria

te question (male participants are not asked PAP SMEAR questions - a 

response code of 9 is automatically added to participant's answer for 

jumped questions), or whether the person decided to walkaway and leave the 

survey (leaving a null value as the last response.)



Other projects do not require definite responses so we do not force users 

to fill-in unnecessary responses.



All our surveys are "Script" driven, the application displays each 

question and available responses as text and saves user selections in 

numeric form. This method keeps our databases relatively small and 

accurate.



Hopefully that will answer you question...



_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

________________________________________



CONFIDENTIALITY & PRIVILEGE NOTICE

The information contained in this email message is intended for the named 

addressee only.  If you are not the intended recipient you must not copy, 

distribute, take any action reliant on, or disclose any details of the 

information in this email to any other person or organisation.  If you 

have received this email in error please notify us immediately.



>>> bdbedell@m... 25/05/2001 9:24:51 >>>

Hi,



Real basic question about null values. My agency uses this State 

supplied

Web application to store client information. Data entry is done through 

a

single main form. I've noticed that the designers don't allow any of 

the

fields on the form to contain null values before saving the record. We

must enter either a value, or a code for "not applicable" or a code

for "no information". Most of the fields in question contain text 

values.

So I'm wondering, from an Access application design best practices 

point

of few: should one adopt a strategy such as this to avoid null values 

in

record fields at all costs? In the Access apps I've designed, I've 

allowed

records to contain null fields if that field is irrelevant for a

particular client. I end up using outer joins in queries a lot though as 

a

result. I don't know if this is good or bad or indifferent. Should 

every

field in a saved record contain a value, even if it's just an empty

string, or a 0, or a code for "not applicable" or "no information"? 

-

Thanks All,



That was really helpful. The head of the nail has been hit. If you want 

information, you must require it. And this is all about information. A 

user's non-response, for whatever reason, is informative. Hence, 

unrequired fields are worthless. Fields containing null values tell us 

nothing. Ergo, unrequired fields have no place in a well designed 

database. Look at the output. If you have a report in your hand, and see 

empty fields on it, how do you know if that field didn't apply, or if the 

data entry person had a bad day?  

Hi Bob.  Your statement about unrequired fields having no place is not

always accurate. I have an application where, at the time of entry,  all

information is not known.  The record will then be modified over time as

this info is acquired.



----- Original Message -----

From: "Bob Bedell" <bdbedell@m...>

To: "Access" <access@p...>

Sent: Friday, May 25, 2001 2:10 PM

Subject: [access] Re: Nul Values





> Thanks All,

>

> That was really helpful. The head of the nail has been hit. If you want

> information, you must require it. And this is all about information. A

> user's non-response, for whatever reason, is informative. Hence,

> unrequired fields are worthless. Fields containing null values tell us

> nothing. Ergo, unrequired fields have no place in a well designed

> database. Look at the output. If you have a report in your hand, and see

> empty fields on it, how do you know if that field didn't apply, or if the

> data entry person had a bad day?