Query value between min-max and return another value

[tedzbug]

I am learning access2003 on the fly at my job and am having a real hard
time figuring this out.

I have tables with an ID column, a MIN column, a MAX column and
multiple columns following with values that correspond to the ranges
IE:

ID MIN MAX STYLE1 STYLE2 STYLE3
1 .125 .250 2.5 5.0 2.0
2 .251 .375 3.0 6.0 2.5
3 .376 .425 3.5 7.0 3.0


The problem i have is that i have a form that i type in a diameter
(t_majordia) and also select a style in a drop down box (t_point_st)

I would like a query that takes my number and compares to min max and
also style to get my values from the table.................

I am kinda lost here.
Please help
Ted Z

 

[kingston via AccessMonster.com]

Create a query based on the table you show and use the following criteria for
the fields (all on the same line):
MIN Criteria: <=[Forms]![FormName]![t_majordia]
MAX Criteria: >=[Forms]![FormName]![t_majordia]
STYLE1 Criteria: =[Forms]![FormName]![t_point_st]

Then on another line, use the same criteria except put the style criteria in
the column for STYLE2. Do the same for STYLE3 and so on.

 

[Marshall Barton]

I am learning access2003 on the fly at my job and am having a real hard
time figuring this out.

I have tables with an ID column, a MIN column, a MAX column and
multiple columns following with values that correspond to the ranges
IE:

ID MIN MAX STYLE1 STYLE2 STYLE3
1 .125 .250 2.5 5.0 2.0
2 .251 .375 3.0 6.0 2.5
3 .376 .425 3.5 7.0 3.0


The problem i have is that i have a form that i type in a diameter
(t_majordia) and also select a style in a drop down box (t_point_st)

I would like a query that takes my number and compares to min max and
also style to get my values from the table.................

When creating a database, the most important and first thing
to do is design a properly Normalized table structure. If
you don't pay careful attention to the rules of
normaization, everthing else you do will be complicated. or
even near impossible. One godd indicator of an unnormalized
table is "multiple columns" with related data. It is
fundamental that spreadsheet thinking must be discarded in
favor of relational thinking.

To make sense of your problem, you need two tables:

table Ranges
ID Long (primary key)
Low Currency
High Currency

table Styles
Key AutoNumber(?) (primary key)
RangeID Long (foreign key to Ranges)
Style Text
StyleVal Currency

Note that I suggest using Currency data type because it is
the least likely to introduce small rounding/conversion
errors for number with no more than four decimal places.


With this structure, you no longer have to worry about which
field has the style value. The query would then be
something like:

SELECT StyleVal
FROM Ranges INNER JOIN Styles
ON Ranges.ID = Styles.RangeID
WHERE Forms!someform.t_majordia Between Low And High
AND Style = Forms!someform.t_point_st

 

[not.unabomber]

Where does my style data go? 2nd chart? Multiple styles in the second
chart, correct?


thanks guys

 

[tedzbug]

Also, What goes in the rangeID & StyleVal columns?

sorry for all the basic Q's, I really appreciate it.

 

[Marshall Barton]

Chart? What chart?

If you designed your table with the idea of using it as the
record source of a chart object, then you hace the cart
before the horse. First, design the tables according to the
rules of normalization, then create queries for specific
uses of the data.

If you are not familiar with the first three rules of
normalization (called Normal Forms), then you need to do
some homework on Google or at your local library.

You have not explained what you mean by "style data". Your
previous post said there is one numeric value associated
with a style so that's what my proposed design accomodated.

 

[Marshall Barton]

Here's the way your posted data would be in the two tables:

Ranges:
ID Low High
1 .125 .250
2 .251 .375
3 .376 .425

Styles:
Key RangeID Style StyleVal
1 1 STYLE1 2.5
2 1 STYLE2 5.0
3 1 STYLE3 2.0
4 2 STYLE1 3.0
5 2 STYLE2 6.0
6 2 STYLE3 2.5
7 3 STYLE1 3.5
8 3 STYLE2 7.0
9 3 STYLE3 3.0

Because the Style field has the same data in multiple rows,
you should have a third table with the style description and
a numeric key. The numeric key would then be used in the
style field in the Styles table.

One vague summary of normalization is the question: How
many things have to be changed when a field is added or
edited? The answer to the question should always be: One
field in one row in one table and no code. Do some homework
and think about it, eventually all will become clear ;-)

 

[Jamie Collins]

Note that I suggest using Currency data type because it is
the least likely to introduce small rounding/conversion
errors for number with no more than four decimal places.

The CURRECY data type performs 'banker's rounding' by nature, which is
not always wanted.

The DECIMAL type would be more appropriate: no rounding errors - no
rounding! - and you can choose the number of decimal places to suit;
store an extra decimal place means you can do you own custom rounding.
And it doesn't get mistaken for monetary data <g>.

Jamie.

--

 

[Marshall Barton]

The CURRECY data type performs 'banker's rounding' by nature, which is
not always wanted.

The DECIMAL type would be more appropriate: no rounding errors - no
rounding! - and you can choose the number of decimal places to suit;
store an extra decimal place means you can do you own custom rounding.
And it doesn't get mistaken for monetary data <g>.

True, but I hesitate to mention it because there are gaps in
Access' support of the Decimal data type.

Note that all values that display a lower precision than the
actual value are rounded using the bankers rounding method.
In this regard, the Currency data type is no different than
any other numeric data type. The big difference between
Currency and a floating point data type is that Currency is
a Fixed Point number that converts numbers of four or fewer
decimal placed exactly. Floating point numbers convert a
number to a binary value that may not be precisely the same
as the number that was entered (e.g. 0.1) See
http://docs.sun.com/source/806-3568/ncg_goldberg.html
for more details than you ever wanted to know ;-)

 

[Jamie Collins]

True, but I hesitate to mention it because there are gaps in
Access' support of the Decimal data type.

Gaps? Can you tell me what you mean, please?

Can you also spare a few minutes to review this recent thread (link
below) and add any comments you have?

Jet 4.0 DECIMAL type: VBA Decimal type and take up in Access
http://groups.google.com/group/microsoft.public.access/msg/076891d5343a0461?hl=en&

Thanks in advance.

Note that all values that display a lower precision than the
actual value are rounded using the bankers rounding method.

That is not true of all numeric types. For example, as I mentioned
above, the DECIMAL type does not round as such; rather, it exhibits
symmetric truncation e.g. to test:

CREATE TABLE Test (
cur_col CURRENCY NOT NULL,
dec_col DECIMAL(19,4) NOT NULL
)
;
INSERT INTO Test VALUES (0.55555, 0.55555)
;
SELECT cur_col, dec_col
FROM Test
;
returns 0.5556 (banker's rounding) and 0.5555 (symmetric truncation)
respectively.

The big difference between
Currency and a floating point data type is that Currency is
a Fixed Point number that converts numbers of four or fewer
decimal placed exactly.

The DECIMAL type is also a fixed point type, called an 'exact numeric'
type in SQL-92 standard (note there is no CURRENCY the SQL standard). I
don't know what you mean by "four or fewer decimal [places] exactly":
we already agreed that CURRENCY will exhibit banker's rounding at four
decimal places but IMO that does not translate to 'exactness',
especially if your client requires four decimal places using an
alternative rounding algorithm (or more e.g. five decimal places is not
uncommon, even for genuine currency market data).

Jamie.

--

 

[Jamie Collins]

Note that all values that display a lower precision than the
actual value are rounded using the bankers rounding method.
In this regard, the Currency data type is no different than
any other numeric data type.

DOUBLE PRECISION does not exhibit banker's rounding. To test:

The Jet SQL function ROUND()exhibit banker's rounding (see
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652) and can
be used to test the result. The following values (of type DECIMAL) are
rounded as follows:

SELECT ROUND(0.1234567890123425, 15), ROUND(0.1234567890123435, 15)

returns 0.123456789012342 and 0.123456789012344 respectively. The last
two figures are significant: 25 rounds towards zero to become 2 and 35
rounds away from zero to become 4.

Contrast the above results with the following, where the same values
are cast as DOUBLE PRECISION then coerced back to DECIMAL (by operating
on the results using 0.1, being a DECIMAL value of insignificant
scale/precision):

SELECT CDBL(0.1234567890123425) + 0.1 - 0.1, CDBL(0.1234567890123435) +
0.1 - 0.1

returns 0.123456789012342 and 0.123456789012343 respectively i.e. 25 is
truncated to become 2 and 35 is similarly truncated to become 3.

Jamie.

--

 

[Marshall Barton]

There is no question that Decimal is more precise as a fixed
point data type, that's its purpose.

I am not sure that what you are seeing via Jet is the same
thing you would get in VBA. The entry of a number that's
too large for the data type seems to just ignore the extra
characters (same effect as truncation).

Your example re Currency used 5 places so it fall outside
the limt of four places I was discussing.

VBA does not support Decimal as a native variable type. It
is only available as a subtype of the Variant type. With
all of Access/VBA implicit data conversions going on I
really have no idea if Decimal will be maintained throughout
the calculation of an expression. Expressions in queries
are notorious for losing track of the data type and I am
leery of introducing another datatype just to squeeze out a
little more precision unless it's absolutely essential.

 

[Jamie Collins]

VBA does not support Decimal as a native variable type. It
is only available as a subtype of the Variant type.

Many thanks for your reply.

One thing: do you think it significant that VBA does not have a
*native* Decimal type? I tried to cover all the points in the following
thread:

Jet 4.0 DECIMAL type: VBA Decimal type and take up in Access
http://groups.google.com/group/microsoft.public.access/msg/076891d5343a0461?hl=en&

Have I missed something?

Thanks again.

Jamie.

--

 

[Marshall Barton]

Many thanks for your reply.

One thing: do you think it significant that VBA does not have a
*native* Decimal type? I tried to cover all the points in the following
thread:

Jet 4.0 DECIMAL type: VBA Decimal type and take up in Access
http://groups.google.com/group/microsoft.public.access/msg/076891d5343a0461?hl=en&

Have I missed something?

Significant?? I think that's highly subjective, but because
of the other points I stated, I won't use it without a very
good reason.

I think you may be making valid points there, but the need
for the Decimal datatype (like the fixed length string) is
not great. This means that very few people are willing to
learn all of its nuances and thus it is avoided (as I do)
without a pressing need for the higher precision. Nothing
can eliminate rounding errors in any limitted precision
system that are introduced by multiplication, division,
etc, especially when the scale of the values varies, which
is where the issue is important.

I guess I'm saying that a good part of the answer to your
question is psychological. Way back when I wan in grad
school, we had a variable length number kind of computer.
Since this was the only kind of number, most people
specified the number width to be 100+ digits and then
complained about how slow the computer was. A brief
analysis of the calculations they were doing usually
determined that fewer than a dozen digits would be
sufficient and performance improved dramatically. Of course
computers are incredibly faster now and the variable length
number kind of machines are long gone, but user attitudes
are frequently formed by factors far from any specific
technical considerations.

 

[Jamie Collins]

<<snipped>> I guess I'm saying that a good part of the answer to your
question is psychological.

This is along the lines that I have been thinking. When I read phrases
such as 'wildly inaccurate' to describe the predicable (but wrong)
results of the DECIMAL sort order bug and claims that DECIMAL NULL
values appear anywhere but at the end of a resultset (an unreproducible
violation of Jet's NULL collation order) I tend to think that such a
response is not entirely rational.

That you for taking the time to provide a full reply.

Jamie.

--

 

[Marshall Barton]

This is along the lines that I have been thinking. When I read phrases
such as 'wildly inaccurate' to describe the predicable (but wrong)
results of the DECIMAL sort order bug and claims that DECIMAL NULL
values appear anywhere but at the end of a resultset (an unreproducible
violation of Jet's NULL collation order) I tend to think that such a
response is not entirely rational.

I really do not want to critique Allen's words here.
Apparently you feel that some other phasing would be more
appropriate (which is certainly your right), but I strongly
object to ascribing any attribute of a person's motivation
or mental state based on anything as flimsy as a phrase used
to describe a bug.

 

[Jamie Collins]

I really do not want to critique Allen's words here.
Apparently you feel that some other phasing would be more
appropriate (which is certainly your right), but I strongly
object to ascribing any attribute of a person's motivation
or mental state based on anything as flimsy as a phrase used
to describe a bug.

I was concurring your point about 'psychological' aspects. I'll be
clear: I did not intend to suggest *psychosis* as regards any
individual and apologise if it came across that way. I would guess the
author of the 'wildly inaccurate' quote was motivated by frustration,
annoyance and embarrassment of being caught out by this bug with a
client and those are motivations I can certainly appreciate. Shame I
have to guess...

I appreciate you do not want to critique the words used but what about
the content? The article contains a number of misstatements (e.g. the
non-existent NULL collation 'bug') which contribute to the overall
impression IMO that the effects of the bug have been exaggerated.
Considering the article is often referenced in these groups, don't you
think accuracy is of importance?

Thanks again.

Jamie.

--

 

[Marshall Barton]

I was concurring your point about 'psychological' aspects. I'll be
clear: I did not intend to suggest *psychosis* as regards any
individual and apologise if it came across that way. I would guess the
author of the 'wildly inaccurate' quote was motivated by frustration,
annoyance and embarrassment of being caught out by this bug with a
client and those are motivations I can certainly appreciate. Shame I
have to guess...

I appreciate you do not want to critique the words used but what about
the content? The article contains a number of misstatements (e.g. the
non-existent NULL collation 'bug') which contribute to the overall
impression IMO that the effects of the bug have been exaggerated.
Considering the article is often referenced in these groups, don't you
think accuracy is of importance?

What kind of question is that, of course I think accuracy is
important.

Allen's sample table does not demonstrate Null sorting
randomly and I have not personally seen the issue (because I
have no need to use Decimal type), but I believe there is
such a case. Allen is too careful and digs into this kind
of thing too deeply to make a blatently false statement out
of sheer frustration. I'm just grateful that he's willing
to spend the time to document problems for the rest of us.

 

[Jamie Collins]

Allen's sample table does not demonstrate Null sorting
randomly and I have not personally seen the issue (because I
have no need to use Decimal type), but I believe there is
such a case. Allen is too careful and digs into this kind
of thing too deeply to make a blatently false statement out
of sheer frustration.

I don't want to be harsh on anyone. Just this week I broke my own rule
about filing a bug report in 'anger' and recorded a false statement out
of sheer frustration, not from spite but because I didn't revisit my
steps to reproduce and assumed I knew the cause of what I'd seen, etc.

This isn't a matter of 'trust' or 'reputation'. Even the best
scientists and mathematicians make mistakes; that's why a proof is
produced for peer scrutiny which benefits everyone, including the
author. The word 'proof' is not pejorative here; it is not meant to
imply distrust. How can we start looking to solve or ameliorate a
problem if we can't reproduce it, let alone determine causation.

As you point out, we have the mention of a further bug - null collation
- but no proof, no hint of how to reproduce it. So let's use reason.

Not every aspect of the Jet engine is documented, and some which still
could not be described as 'supported' functionality. Null collation,
however, is documented, supported and is to be relied upon.

Don't you think that if there was a known issue with null collation it
would be a more serious problem that a DECIMAL sort order bug that only
affects part of a resultset when the order is descending and includes
negative as well as positive values and/or zeros? I do. I'd certainly
want more details on the status of any null collation bug e.g. what
other functionality is affected e.g. GROUP BY? I'd expect a bug report
from Microsoft. MVPs report bugs to Microsoft, right?

I'm just grateful that he's willing
to spend the time to document problems for the rest of us.

I know what you are doing. Don't get me wrong: I think it's admirable
to show support for each other. The MVP is a good 'brand' because of
it. But I don't think such issues are about having a good track record
(and Allen deservedly has one of the best in the Access community).

But I can't agree your sentiments. I think that being in a position of
trust and mentioning an apparent bug but failing to providing steps to
reproduce is like

 

[jlepack]

I hope OP got his question answered elsewhere.

I don't want to be harsh on anyone. Just this week I broke my own rule
about filing a bug report in 'anger' and recorded a false statement out
of sheer frustration, not from spite but because I didn't revisit my
steps to reproduce and assumed I knew the cause of what I'd seen, etc.

This isn't a matter of 'trust' or 'reputation'. Even the best
scientists and mathematicians make mistakes; that's why a proof is
produced for peer scrutiny which benefits everyone, including the
author. The word 'proof' is not pejorative here; it is not meant to
imply distrust. How can we start looking to solve or ameliorate a
problem if we can't reproduce it, let alone determine causation.

As you point out, we have the mention of a further bug - null collation
- but no proof, no hint of how to reproduce it. So let's use reason.

Not every aspect of the Jet engine is documented, and some which still
could not be described as 'supported' functionality. Null collation,
however, is documented, supported and is to be relied upon.

Don't you think that if there was a known issue with null collation it
would be a more serious problem that a DECIMAL sort order bug that only
affects part of a resultset when the order is descending and includes
negative as well as positive values and/or zeros? I do. I'd certainly
want more details on the status of any null collation bug e.g. what
other functionality is affected e.g. GROUP BY? I'd expect a bug report
from Microsoft. MVPs report bugs to Microsoft, right?


I know what you are doing. Don't get me wrong: I think it's admirable
to show support for each other. The MVP is a good 'brand' because of
it. But I don't think such issues are about having a good track record
(and Allen deservedly has one of the best in the Access community).

But I can't agree your sentiments. I think that being in a position of
trust and mentioning an apparent bug but failing to providing steps to
reproduce is like