Prev: Linked Tables - indexes
Next: pasted record in locked form crashes database
From: Michael Conroy on 25 Mar 2010 17:15
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
From: Jerry Whittle on 25 Mar 2010 17:27
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
From: Jeff Boyce on 25 Mar 2010 17:30
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" <MichaelConroy(a)discussions.microsoft.com> wrote in message
news:877A17F2-C7A7-4F24-ABAF-0F24562241E1(a)microsoft.com...
> 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


From: Michael Conroy on 25 Mar 2010 17:39
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
From: John W. Vinson on 25 Mar 2010 22:13
On Thu, 25 Mar 2010 14:15:01 -0700, Michael Conroy
<MichaelConroy(a)discussions.microsoft.com> 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]
 |  Next  |  Last
Pages: 1 2
Prev: Linked Tables - indexes
Next: pasted record in locked form crashes database