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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|