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.

 

[tedzbug]

I apologize for wrong terminology, Table not Chart, is what i meant.
Still a lot of learning to go.

I now am making more sense of the multiple chart layout. i was doing
the same as most and trying to put all my data into as few tables as
can be..........

Thankyou very much for your thorough explanation.

Ted

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 ;-)
--
Marsh
MVP [MS Access]

Also, What goes in the rangeID & StyleVal columns?

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

 

[tedzbug]

I am just aking to clarify for myself, Can i run the table like this?:

I am picking a style on my main form and also entering a diameter that
is compared to the min max to give me the range.............. and the
resulting field is from the table below.


Styles:

(key)
RangeID Style1 Style2 Style3 Style4 Style5
1 2.5 5.0 2.0 3.0 4.0
2 3.0 6.0 2.5 3.5 4.5
3 3.5 7.0 3.0 4.0 5.0
4 4.0 8.0 4.0 4.5 5.5

I apologize for wrong terminology, Table not Chart, is what i meant.
Still a lot of learning to go.

I now am making more sense of the multiple chart layout. i was doing
the same as most and trying to put all my data into as few tables as
can be..........

Thankyou very much for your thorough explanation.

Ted

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 ;-)
--
Marsh
MVP [MS Access]

Also, What goes in the rangeID & StyleVal columns?

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


Marshall Barton wrote:

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

 

[Marshall Barton]

Well, all kinds of things are possible with enough clever
VBA code, but in all practicality, that design has the same
problems as your original design.

Minimizing the number of tables is not a viable goal. If
you want to focus on something like that, then minimizing
the number of columns in each table will produce far better
results. One semi-famous quote is "Rows are cheap but
columns are expensive."

The ultimate goal is to properly normalize your tables'
design. If you continue to think like a spreadsheet, your
project will eventually become so complex that you may not
be able to make sense of it. Do the homework on
Normalization. There are no shortcuts, but, for our
purposes, it's not a particularly deep subject. There's a
short introductory KB article at:
http://support.microsoft.com/kb/283878/en-us

 

[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 ;-)

 

[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.

 

[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.

 

[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.

 

[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.

 

[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

 

[Marshall Barton]

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

MVPs have no control over what bug information Microsoft
makes available to the public.

Since you were responding to my post I thought you were
asking my opinion. If you want more than that, you should
take it up with Allen.

 

[Marshall Barton]

I hope OP got his question answered elsewhere.

It may not be the answer he wanted, but it was answered.
Since he hasn't come back in the last several days, I assume
that part of the thread is either closed or at least
dormant.