A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

To Zero or not to Zero



 
 
Thread Tools Display Modes
  #1  
Old March 25th, 2010, 10:15 PM posted to microsoft.public.access.tablesdbdesign
Michael Conroy
external usenet poster
 
Posts: 42
Default To Zero or not to Zero

This is more of a design question about how data should be stored in a table.
While importing information from Excel, some records in the same field have
zeros and others don't, meaning they are empty. All the fields in question
are numeric. So I am looking at an update query to clean up the table and I
have two choices. I can enter a null value if it equals zero or a zero value
if it equals null. The zero value will make formulas easier, meaning the nz
won't be needed, but the size of the table will increase (OK, maybe not that
much). On the other hand, the fields are like credits and debits, for each
record you will get one number and the other will always be nothing, so the
zero value doesn't really mean anything to me. So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

As always, thanks in advance for any help
--
Michael Conroy
Stamford, CT
  #2  
Old March 25th, 2010, 10:27 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default To Zero or not to Zero

Null means that you don't know. It's like eye color. You assume that people
have eyes, but you don't know everyone's eye color.

0 means nothing. I own 0 aircraft.

Therefore if 0 makes sense in your data (0 credit or debit), I would go with
that especially for the reasons that you gave such as not dealing with NZ and
other null pitfalls.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Michael Conroy" wrote:

This is more of a design question about how data should be stored in a table.
While importing information from Excel, some records in the same field have
zeros and others don't, meaning they are empty. All the fields in question
are numeric. So I am looking at an update query to clean up the table and I
have two choices. I can enter a null value if it equals zero or a zero value
if it equals null. The zero value will make formulas easier, meaning the nz
won't be needed, but the size of the table will increase (OK, maybe not that
much). On the other hand, the fields are like credits and debits, for each
record you will get one number and the other will always be nothing, so the
zero value doesn't really mean anything to me. So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

As always, thanks in advance for any help
--
Michael Conroy
Stamford, CT

  #3  
Old March 25th, 2010, 10:30 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default To Zero or not to Zero

Michael

Are you saying that your table structure has one field for Debits and one
field for Credits? If so, you're pretty much guaranteeing that one will be
unused in each transaction row, right?

A couple thoughts ...

First, if you don't have a value, use a null. But a "0" IS a meaningful
value ... it means "I don't have any", not "I don't know" (i.e., Null).

Second, rather than force that kind of double-injury bookkeeping approach on
Access, what about the idea of a table structure like:

tblTransaction
TransactionID
TransactionAmount
DrCr (use this field to indicate whether the amount is a debit or
credit)

(yes, I know, this is grossly over simplified, but what about the approach
of using a single field for the amount instead of two?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Michael Conroy" wrote in message
...
This is more of a design question about how data should be stored in a
table.
While importing information from Excel, some records in the same field
have
zeros and others don't, meaning they are empty. All the fields in question
are numeric. So I am looking at an update query to clean up the table and
I
have two choices. I can enter a null value if it equals zero or a zero
value
if it equals null. The zero value will make formulas easier, meaning the
nz
won't be needed, but the size of the table will increase (OK, maybe not
that
much). On the other hand, the fields are like credits and debits, for each
record you will get one number and the other will always be nothing, so
the
zero value doesn't really mean anything to me. So my question is this,
what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

As always, thanks in advance for any help
--
Michael Conroy
Stamford, CT



  #4  
Old March 25th, 2010, 10:39 PM posted to microsoft.public.access.tablesdbdesign
Michael Conroy
external usenet poster
 
Posts: 42
Default To Zero or not to Zero

Jerry,
The knowing/not knowing part was key. In my case if the transaction has a
credit then I know the debit was zero for that record and versa visa.
Thanks for clearing it up.

--
Michael Conroy
Stamford, CT


"Jerry Whittle" wrote:

Null means that you don't know. It's like eye color. You assume that people
have eyes, but you don't know everyone's eye color.

0 means nothing. I own 0 aircraft.

Therefore if 0 makes sense in your data (0 credit or debit), I would go with
that especially for the reasons that you gave such as not dealing with NZ and
other null pitfalls.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Michael Conroy" wrote:

This is more of a design question about how data should be stored in a table.
While importing information from Excel, some records in the same field have
zeros and others don't, meaning they are empty. All the fields in question
are numeric. So I am looking at an update query to clean up the table and I
have two choices. I can enter a null value if it equals zero or a zero value
if it equals null. The zero value will make formulas easier, meaning the nz
won't be needed, but the size of the table will increase (OK, maybe not that
much). On the other hand, the fields are like credits and debits, for each
record you will get one number and the other will always be nothing, so the
zero value doesn't really mean anything to me. So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

As always, thanks in advance for any help
--
Michael Conroy
Stamford, CT

  #5  
Old March 26th, 2010, 03:13 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default To Zero or not to Zero

On Thu, 25 Mar 2010 14:15:01 -0700, Michael Conroy
wrote:

So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?


I'd ask "Does a blank field have a different interpretation than a zero"? In
some cases it may not - a credit is a good example, where a zero default (and
nulls being forbidden) makes sense. In other situations they might indeed be
different - "How many birds are on my lawn" might be zero (I looked and there
weren't any) or it might be null (I haven't looked and don't have any idea how
many).

It's got to be decided on the basis of the real-life situation.
--

John W. Vinson [MVP]
  #6  
Old April 2nd, 2010, 12:32 AM posted to microsoft.public.access.tablesdbdesign
Michael Conroy
external usenet poster
 
Posts: 42
Default To Zero or not to Zero

Gentlemen,
I have read all the responses and I thank you for the input. John,
normalizing the data would eliminate null values but my source file is a
little more complicated. I just found out some airports use the GrossIn fuel
amount, some use the NetIn and both fields can have a value so it is not
exactly like a credit debit. I could still normalize it and add a qualifier
that labels it Gross or Net, but I am somewhat leary of modifying source
information if I can use a query to grab what I want. So I will add this
gross/net "switch" to the airport table and use that to grab the correct
column from the fuel table.

Additionally, I just wanted to add that time is a factor in this discussion.
As I mentioned in the original post I am importing a spreadsheet from another
company that tells us how much fuel is pumped into each plane. I realize I
failed to mention that the data is historical and will never be altered. So
seeing a null value does not mean that they have not pumped the fuel yet, it
means they just never put a zero in the spreadsheet cell. Since I am
normalizing other fields before the data gets added to the main table, in
this case, I feel comfortable adding zeros to their source information to
make my life easier down the road.

So with dynamic data, a zero means the tank is empty, null means you did not
look yet. Thanks again and sorry for the late reply.
--
Michael Conroy
Stamford, CT


"John W. Vinson" wrote:

On Thu, 25 Mar 2010 14:15:01 -0700, Michael Conroy
wrote:

So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?


I'd ask "Does a blank field have a different interpretation than a zero"? In
some cases it may not - a credit is a good example, where a zero default (and
nulls being forbidden) makes sense. In other situations they might indeed be
different - "How many birds are on my lawn" might be zero (I looked and there
weren't any) or it might be null (I haven't looked and don't have any idea how
many).

It's got to be decided on the basis of the real-life situation.
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.