Blank Fields

[Raj]

I hope this is the correct newsgroup for this question.
I am creating a database from data that I imported from a
spreadsheet. I have over 70,000 row and 47 columns of
information, and it contain alot of 0 and $0.00 and blank
fields. For better performance should I remove the 0 and
$0.00 and leave plank or replace the blank fields with 0
and $0.00.
Thank you for the help.

 

[John Vinson]

For better performance should I remove the 0 and
$0.00 and leave plank or replace the blank fields with 0
and $0.00.

The only effect on performance would be that you would have to search
using two criteria - =0 OR IS NULL - instead of just one. Typically
NULL and zero have different meanings: a blank, or NULL, field means
"this field has no defined value" and 0 means "this field has a known,
precise value, it's zero".

You can run an UPDATE query updating each such field to

NZ([fieldname])

This will convert all the NULL values to zero, leaving non-null values
alone.

 

[Tim Ferguson]

and it contain alot of 0 and $0.00 and blank
fields. For better performance should I remove the 0 and
$0.00 and leave plank or replace the blank fields with 0
and $0.00.

There is a question that comes before this one: what do you mean by zero or
null in the context of your data? A null is generally taken to mean unknown
or inapplicable -- the cost of an item that has not yet been bought, for
example. A zero is an amount -- equal to one more than four minus five, or
the price of a free trip or the number of items in stock for something
you've just run out of.

Only you can know how many of your zeroes are actually blanks (and prime
for turning into nulls) and how many are real zero values (and should be
left alone).

HTH


Tim F