Best way to store calc result that can be "NA"?

[PeteCresswell]

I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

?

[PeteCresswell]

On Tue, 22 Feb 2005 15:38:55 -0500, PeteCresswell <x@y.z> wrote:

staged beforehand via a batch job

I should add that right now it looks like these fields are the final
result tb used on various reports. i.e. they do not have to be
summed....(crossed fingers....)

[PeteCresswell]

And, while I'm at it...

Is there any downside to storing amounts as percentages instead of
storing them as decimals and changing to percent for display.

I've always stored as decimal numbers and comverted...but somebody
thinks it would be better to store as percent.....

[Tim Marshall]

PeteCresswell wrote:

I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields. My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

I am definitely not a greater mind than yours with respect to Access,
but what about...

A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases.theory!!!!) with the
relational way of doing things (ie, not storing nulls as data).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[paii, Ron]

Why don't you add a 2nd field to show status of the calculated field? That
way you can note among other things that the calculated value is now invalid
because the input values have changed.

"PeteCresswell" <x@y.z> wrote in message
news:fq********************************@4ax.com...

I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

?

[PeteCresswell]

On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases.theory!

But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.

[PeteCresswell]

One more (unrelated) thing:

Is it just me, or are there "Re" literals proliferating in my replies?

My current subject line looks like:

"Re: Re: Re: Best way to store calc result that can be "NA"?"

Some setting in my newsreader (Agent)?

[Ken Snell]

If you have a defined range for the values that are "acceptable" for that
field (say -9999 to +9999), you could use a value that is way out of range
(-99999999) and then do a test for a value that is less than -99999990 and
use that to say that the Calc couldn't do the job.

Otherwise, using a flag field would be a reasonable solution.

--

Ken Snell
<MS ACCESS MVP>

"PeteCresswell" <[e-mail]> wrote in message
news:lt********************************@4ax.com...

On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

A null would be a result of the bad calculation you mentioned. I assume
you're working in Jet, which allows you to update a double to a null.

Null means no value, which is in keeping with an error or insufficient
data and could act as a flag for bad calcs and still be in keeping (I
think, better not post this on comp.databases.theory!

But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.

[jimfortune]

PeteCresswell wrote:

On Tue, 22 Feb 2005 17:24:27 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

A null would be a result of the bad calculation you mentioned. I assumeyou're working in Jet, which allows you to update a double to a null.
Null means no value, which is in keeping with an error or insufficientdata and could act as a flag for bad calcs and still be in keeping (Ithink, better not post this on comp.databases.theory!

But I have two "Null" situations:

- Calc couldn't do the job
- We've never even tried.

But the text thing is really bothering me..... maybe Ron's idea about
flag fields to explain the reason for Null.....and a *lot* of IIF
statements throughout the app...

What I'm really trolling for here is somebody that's actually been
this route.... Lacking that, I'd have to say my heart is with you and
Ron.

I haven't been this route before. Plus, I'm not a greater Access mind.
How about leaving the field as Decimal and using, say, -10000 for
recalcitrant situations? Then convert -10000 to "(NA)" at the output
stage maybe with a function that returns CStr(Nz([CalcField],"")) or
"(NA)". Use "WHERE [CalcField] <> -10000" if the values need to be
summed?

James A. Fortune

[Lyle Fairfield]

PeteCresswell wrote:

I was happily coding along, putting some calculation results (rolling
annualized rates of return - too compute-intensive to calculate
on-the-fly - had tb staged beforehand via a batch job) into Double
fields.

Now, the clients have just dropped a litle bomb: they want to see
"(NA)" if the calculation failed due to insufficient input data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and there was
not enough data
My kneejerk is to change these to text fields, but I wonder if greater
minds than mine have come up with a better approach.

You could use

Public Function Indeterminate() As Double
On Error Resume Next
Indeterminate = 1E+308
Indeterminate = Indeterminate + 1E+308
Indeterminate = Indeterminate * 0
End Function

to return a number that should display as

-1.#IND

(This function should be credited primarily to Vincent Quesnoit.)

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....

[Bob Quintal]

PeteCresswell <x@y.z> wrote in
news:cj********************************@4ax.com:

And, while I'm at it...

Is there any downside to storing amounts as percentages
instead of storing them as decimals and changing to percent
for display.

I've always stored as decimal numbers and comverted...but
somebody thinks it would be better to store as percent.....

And someone surely thinks the world is flat.
Since a percentage is by definition the ratio of two numbers, the
"correct" way is to never store the ratio but calculate it when
needed, based on the rationale that somebody will forget to
change the result after changin one of the two numbers.

Now I realize you said that you are precalculating some values,
so that may mitigate the lack of correctness, but I would
hesitate to go further than what is actually necessary in the
pre-calculation, therefore I would convert from a fraction to a
percent at display time, never before.

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

PeteCresswell <x@y.z> wrote in
news:fq********************************@4ax.com:

I was happily coding along, putting some calculation results
(rolling annualized rates of return - too compute-intensive to
calculate on-the-fly - had tb staged beforehand via a batch
job) into Double fields.

Now, the clients have just dropped a litle bomb: they want to
see "(NA)" if the calculation failed due to insufficient input
data.

That means our fields can have three kinds of values:

- Null: (something to say we've never even tried to make the
calc)

- Decimal: (let's say -99.00 to +99.99 - including zero)

- CalcDidNotWork: Something that says we tried the calc and
there was not enough data
My kneejerk is to change these to text fields, but I wonder if
greater minds than mine have come up with a better approach.

?

I handle this sort of situation by including a second field that
returns the value type (calculation status), say an integer type
with 1,2,3 for your 3 kinds.

At display time, use a case structure or nested iif() functions
to display what is needed.
--
Bob Quintal

PA is y I've altered my email address.

[(Pete Cresswell)]

Per Bob Quintal:

but I would
hesitate to go further than what is actually necessary in the
pre-calculation, therefore I would convert from a fraction to a
percent at display time, never before.

That's always been my gut-level feeling - but I never had the words to back it
up...
--
PeteCresswell

[(Pete Cresswell)]

Per Bob Quintal:

I handle this sort of situation by including a second field that
returns the value type (calculation status), say an integer type
with 1,2,3 for your 3 kinds.

At display time, use a case structure or nested iif() functions
to display what is needed.

I think that's where I'm going.

Using wildly out-of-range values called out to me for a short time...but it
seems like I'd be exposing myself to unforseen situations like SUM() and
whatever other calculations might be made - which already nailed me on the input
side before it dawned on me that Lipper indicates a Null return value with
-99.99 and PSN indicates same with a -999.

--
PeteCresswell

[PeteCresswell]

On Tue, 22 Feb 2005 19:29:56 -0500, "(Pete Cresswell)" <[e-mail]>
wrote:

I handle this sort of situation by including a second field that
returns the value type (calculation status), say an integer type
with 1,2,3 for your 3 kinds.

Just as a point of interest; I thought "Well, this is an
under-the-covers sort of thing, so why not just use a text field and
populate it with a literal value describing the situation...no table
to link to, no translation when somebody wants to see what's
happening..

Turns out that Text fields are a lot more expensive than Longs.... The
DB size lept from 44 megs to a half-gig....

So I'm back to doing exactly what you suggest - with a little system
table "zstblCalcStat" to document the values, but no RI on the values.

[Bri]

Pete,

It gets even stranger. I'm using Mozilla 1.4.1 and in the Thread Pane
there is only one Re:, but in the Message Pane it shows the three ("Re:
Re: Re: Best...). OK, now I notice that the Subject in this Reply also
only has the one Re: in it. One least thing to check... Yup, the message
source of your message shows the three of them. In my case I would say
that Mozilla figures out that there are redundant ones and filters them
out when viewing the subject (except the Message Pane and the Source).

--
Bri

PeteCresswell wrote:

One more (unrelated) thing:

Is it just me, or are there "Re" literals proliferating in my replies?

My current subject line looks like:

"Re: Re: Re: Best way to store calc result that can be "NA"?"

Some setting in my newsreader (Agent)?

[(Pete Cresswell)]

Per Bri:

In my case I would say
that Mozilla figures out that there are redundant ones and filters them
out when viewing the subject (except the Message Pane and the Source).

It's got tb something with my Agent setup bc the copy I run at home does not do
that - yet the copy I run at a client site does.
--
PeteCresswell